SkillBuilders Welcome  |  Log in
All Oracle Classes

SkillBuilders Oracle PL/SQL Programming Workshop:
3 Day Onsite or 21 Hour Online Hands-On Training

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, scalable PL/SQL programs is an important objective of this course. To meet that objective, the course includes instruction on such things as the proper use of bind variables, bulk processing, pipelining, benchmarking different formulations of a routine and profiling PL/SQL programs to identify the code that should be tuned.

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.

Also see these free online tutorials:

Average Student Rating:

Introduction to PL/SQL    

  • What is PL/SQL?    
  • Why Use PL/SQL?    
  • PL/SQL Program Structure    
  • Anonymous Blocks    
  • Compile Errors
  • Sending Output to SQL*Plus    
  • 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    

Language Fundamentals    

  • PL/SQL Statements    
  • PL/SQL Symbols
  • Quoting Mechanism
  • Common PL/SQL Datatypes
  • Declaring Variables
  • Assignment
  • Referencing Sequences (11g)
  • %TYPE Attribute
  • PL/SQL Records    
  • %ROWTYPE Attribute
  • Programmer Defined Records    
  • Variable Scope
  • Nested Blocks
  • Functions
  • Regular Expressions (11g Enhancements)
  • IF Statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • CONTINUE Statement (11g)
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example    
  • Array Methods    

PL/SQL and SQL, Part I Basics

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

PL/SQL and SQL, Part II Cursors

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

PL/SQL and SQL, Part III Bulk Processing    

  • Bulk Processing    
  • Bulk Collect    
  • Bulk DML – FORALL
  • Using Tables of Records (11g)
  • Bulk DELETE
  • Bulk INSERT
  • Bulk UPDATE    
  • Returning into Arrays

Stored Procedures    

  • What is a Stored Procedure?    
  • CREATE PROCEDURE Syntax    
  • Simple Example    
  • Review: Compile Errors    
  • 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    

PL/SQL Functions    

  • What is a Function?    
  • CREATE FUNCTION Syntax    
  • Simple Example    
  • Calling Functions
  • Mixing Argument Types in Function Calls (11g)
  • The Data Dictionary    
  • Table Functions
  • Building a Table Function    
  • Using a Table Function    
  • Pipelined Functions    
  • Pipelined Example    
  • Test Data Generator Example

Error Handling

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

PL/SQL Packages

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

DML Triggers

  • Simple (11g) DML Trigger Concepts
  • Trigger Execution    
  • Trigger Ordering (11g)
  • CREATE TRIGGER Syntax    
  • Row Triggers
  • :OLD and :NEW Reference Variables
  • Trigger Attributes    
  • Audit Trigger Example
  • Derived Value Trigger Example
  • Trigger Restrictions    
  • Compound Triggers (11g)
  • Table Triggers
  • Autonomous Transactions    
  • Autonomous Trigger Example
  • INSTEAD OF Triggers    

Trace and Tune    

  • Introduction to Tuning PL/SQL    
  • DBMS_PROFILER    
  • Profiler Setup    
  • Using Profiler    
  • START_PROFILER and STOP_PROFILER
  • Reporting with PROFSUM
  • Effective Use of Bind Variables
  • Benchmarking with GET_TIME and GET_CPU_TIME
  • Subprogram Inlining (11g)
  • PL/SQL Result Cache (11g)

File I/O Using UTL_FILE    

  • UTL_FILE Concepts    
  • Setup for UTL_FILE
  • Unix File Permissions
  • Opening Files
  • Closing Files    
  • Reading Files    
  • Writing Files - PUT    
  • Writing Files - PUT_LINE    
  • Writing Files - NEW_LINE    
  • Writing Files - PUTF    

Sending Email with UTL_MAIL  

  • Introduction to UTL_MAIL
  • Setup for Emailing from the Database
  • UTL_MAIL Example  
 
SkillBuilders Sample Video