Oracle PL/SQL Programming Workshop
Duration: 3 Days

User Avatar
Geoffrey Wiland
 4.7/5 (50)
$2,700.00

This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.

Learning how to create efficient, maintainable, and scalable PL/SQL programs are important objectives of this course. To meet these objectives, the course includes instruction on such things as the proper use of stored procedures and functions, cursors, bind variables, bulk processing, packages, triggers, error handling and utilities. Hands-on workshops constitute approximately 50% of the course.

Enrollment includes Steven Feuerstein’s top rated text book “Oracle PL/SQL Best Practices“.

Hands-on workshops constitute approximately 50% of the class.

For customer-site training, as with all of our courses, this class is highly customizable to your specific training requirements.

Course Features

  • Lectures 0
  • Quizzes 0
  • Duration 3 Days
  • Skill level All levels
  • Language English
  • Students 0
  • Certificate Yes
  • Assessments Yes

Introduction to PL/SQL    

  • What is PL/SQL?
  • Why Use PL/SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Using SQL Developer
  • Compile Errors
  • Server Output
  • Introduction to Procedures
  • Procedure Compile Errors
  • Procedure Compile Warnings
  • Introduction to Functions
  • Introduction to Packages
  • Querying the Data Dictionary
  • Introduction to Triggers
  • Tools for PL/SQL Development
  • Working in SQL*Plus
  • Workshop

Language Fundamentals

  • PL/SQL Statements
  • PL/SQL Symbols
  • Quoting Mechanism
  • Common PL/SQL Datatypes
  • Declaring Variables
  • Assignment
  • %TYPE Attribute
  • PL/SQL Records
  • %ROWTYPE Attribute
  • Programmer Defined Records
  • Workshop
  • Variable Scope
  • Nested Blocks
  • Functions
  • Regular Expressions
  • IF Statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods
  • Using Arrays for Processing Speed
  • Workshop

PL/SQL and SQL, Part I Basics

  • SELECT INTO Statement
  • Implicit Cursor Loops
  • Workshop
  • DML in PL/SQL
  • Cursor Attributes
  • Embedding DDL
  • Workshop

PL/SQL and SQL, Part II Cursors

  • What is a Cursor?
  • Explicit Cursor Processing
  • Cursor Attributes
  • Workshop
  • Dynamic Cursors
  • Dynamic Cursor Example
  • Variable Scope & Cursors
  • Cursor Parameters
  • Cursor Records
  • Cursor FOR LOOP
  • Workshop
  • Referencing the Current Row
  • FOR UPDATE Example
  • Workshop

PL/SQL and SQL, Part III Bulk Processing    

  • Bulk Processing
  • Bulk Collect
  • Bulk Collect: Fetch with Limit
  • Workshop
  • Bulk Delete: FORALL
  • Bulk Insert – FORALL
  • Advanced FORALL: IN INDICIES OF; IN VALUES OF
  • Returning into Arrays
  • Workshop

Stored Procedures    

  • What is a Stored Procedure?
  • CREATE PROCEDURE Syntax
  • Compile Errors
  • Compiler Warnings
  • Optimizing Compiler
  • Native Compilation
  • Conditional Compilation
  • Procedure Signatures
  • Calling Procedures
  • Procedure Synonyms
  • Referencing Parameters by Name
  • Returning Sets: REF CURSOR
  • Dropping Stored Procedures
  • Procedure Dependencies
  • ALTER COMPILE Statement
  • PL/SQL Procedure Privileges
  • Granting Execute Privilege
  • The Data Dictionary
  • Workshop

PL/SQL Functions    

  • What is a Function?
  • CREATE FUNCTION Syntax
  • Calling Functions
  • The Data Dictionary
  • Workshop
  • Table Functions
  • Building a Table Function
  • Using a Table Function
  • Pipelined Functions
  • Pipelined Example
  • Test Data Generator Example
  • Workshop

Error Handling

  • Types of Errors
  • Runtime Errors
  • Exception Concepts
  • Predefined Named Exceptions
  • EXCEPTION Block Syntax
  • Handling Exceptions
  • Workshop
  • Recovering from Errors
  • Logging Exceptions
  • Debugging with FORMAT_ERROR_BACKTRACE
  • RAISE_APPLICATION_ERROR Statement
  • Workshop
  • Raising Exceptions
  • Raising User Exceptions
  • EXCEPTION_INIT Pragma

PL/SQL Packages

  • Concepts
  • Package Benefits
  • Package Contents
  • Package Specification
  • Package Body
  • Package Privileges
  • Calling Packaged Objects
  • Initialization Code
  • Session Variables
  • Package Body Variables
  • Workshop
  • Package Overloading
  • Workshop
  • Compiling Packages
  • DROP PACKAGE Statement
  • Workshop
  • Sample of Oracle Supplied Packages

DML Triggers

  • DML Trigger Concepts
  • Trigger Execution
  • Trigger Ordering
  • CREATE TRIGGER Syntax
  • Row Triggers
  • :OLD and :NEW Reference Variables
  • Trigger Attributes
  • Audit Trigger
  • Derived Value Trigger
  • Trigger Restrictions
  • Security Privileges
  • Workshop
  • Statement Level Triggers
  • Workshop
  • Autonomous Transactions
  • Autonomous Triggers
  • Workshop
  • INSTEAD OF Triggers
  • Workshop

File I/O Using UTL_FILE    

  • UTL_FILE Concepts
  • Setup for UTL_FILE
  • Permissions
  • Opening Files
  • Closing Files
  • Reading Files
  • Writing Files – PUT
  • Writing Files – PUT_LINE
  • Writing Files – NEW_LINE
  • Writing Files – PUTF
  • Workshop

Sending Email with UTL_MAIL

  • Introduction to UTL_MAIL
  • Setup for Emailing from the Database
  • UTL_MAIL Coding
  • Other Oracle Supplied Packages
author avatar
Geoffrey Wiland

    https://skillbuilders.com/our_team/geoffrey-wiland/

     5/5

    I liked and enjoyed the whole course. I also learned a lot from it. Geoff did a great job explaining the course material in a clear and concise way.

    Thank you very much Geoff for the knowledge you provided during this course and for your patience!
    amal


    Amal Zerhloul
    December 11, 2020
     5/5



    john.fisher@votec.net
    December 11, 2020
     5/5

    Geoff was very patient and very knowledgeable.

    After going over the lesson and before going into the labs on our own, it would help to do an instructor led example that closely resembles what the lab entails. The course is too fast paced unless you are already a knowledgeable person which is unlikely.

    It would be helpful if we could copy the syntax code shown on the PDF slides
    Liliana Rosete Legorreta
    September 18, 2020
     4.4/5

    The instructor is very knowledgeable and very open to the students suggestions about the training.

    Should have more time to cover the course material.

    Daniyal Ahmed
    September 18, 2020
     4.3/5

    Instructor, Geoff, was good - good pace and clearly understandable.

    Either make more workshops that are 'quicker' and cover less concepts, or include better examples of the workshops in the materials and code before it.

    Meeting software was good
    Matthew Davis
    September 18, 2020
     4.8/5


    I would have some more lab time.

    Link Maynard
    April 24, 2020
     4.5/5

    I thought Geoff was a great instructor. I liked the labs that re-enforced the concepts we learned.

    I would have liked to see some more one-on-one breakouts. I also would have liked if the instructor showed more examples, rather than reading examples from the slide. The labs were sometimes more difficult than the examples provided.

    I liked the hand raising aspect. Everything seemed to work really well for the course being all online.
    Vince Yacano
    April 24, 2020
     4.7/5

    Subject matter is very useful and clear for people just learning PL/SQL.


    DeQuane Wallace
    April 24, 2020
     4.9/5

    Stepping and talking through solutions for workshops


    Libby Rubesa
    April 24, 2020
     4.8/5

    I thought that Geoff was very knowledgeable about the subject matter. He never talked down to any of the students and really took time to go over lab answers and walkthroughs. He also made an effort to really get to know each student.

    Maybe a little more lab time or live examples, and less of the powerpoint slides. But overall the class was very good and learning the basics to PL/SQL

    I thought that the software that was used was very accessible and allowed for smooth for the lessons.
    TJ Colleran
    April 24, 2020
    Load More
    $2,700.00
    Details Price Qty
    09/27/21 - 10/01/21show details +$2,700.00 (USD)  

    Free Online Registration Required

    The tutorial session you want to view requires your registering with us.

    It’s fast and easy, and totally FREE.

    And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

     

    ×

    Message modal