Performance Monitoring Experts

Advanced PL/SQL Tips and Techniques

Oracle PL/SQL - Advanced 

This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn tips and techniques on how to advance their expertise when programming in PL/SQL.  This course covers all of the latest features of PL/SQL using databases Oracle 10g, Oracle 11g and Oracle 12c.  This course utilizes a variety of tools, one of which is Quest Software's Toad.  

This class could easily be named "New Features for Developers."

Lectures and topics are enhanced with live illustrations and hands-on exercises.


  1. Review of PL/SQL syntax and language structure.

  2. Review of cursor management.

  3. Review of PL/SQL Profiling.

  4. Review of Package Usage (User Defined and Oracle Defined.

  5. Introduction to advanced PL/SQL topics including compiler options, code encryption, conditional compilation, collections, triggers, dynamic SQL and much more.

Students who wish to learn advanced tips and techniques of PL/SQL programming who already have a working knowledge of SQL and PL/SQL.

Working knowledge of SQL and PL/SQL. 

      o   Study Guide with presentations
      o   PL/SQL Reference pocket guide
      o   Example files with SQL and SQL*Plus scripts
      o   Information on Toad, SQL Developer, using UTL_MAIL and executing external procedures

Day 1:  Review of PL/SQL Options and Compiling
      o   Overview
      o   Review
      o   PL/SQL Compiler Options
      o   Code Encryption
      o   Conditional Compilation
      o   Definer/Invoker Rights
      o   Executing and Exception handling
      o   Autonomous Transactions
      o   Package Options
      o   Overloading

Day 2:  Triggers, Cursors and Collections
       o   Database Triggers
               - DML (Data Manipulation Language)
               - Instead of
               - ServerError
               - Startup
               - Shutdown
               - Logon
       o   Cursors
               - User Defined Datatypes
               - Cursor Sharing
               - Cursor Variables
               - Strong vs. Weak Cursors
       o   Collections
               - Associate Arrays
               - Nested Tables
               - VArrays
               - Bulk Collect/Forall Bulk Binding
               - Using Collections for Reference Table
Day 3:  Oracle Provided Packages and Miscellaneous Topics
​       o   DBMS_ALERT
       o   DBMS_JOB
       o   DBMS_OUTPUT
       o   UTL_FILE
       o   UTL_MAIL
       o   Other Provided Packages
       o   DBMS_SQL and Dynamic SQL
       o   Working with Large Objects
       o   Debugging PL/SQL Routines
       o   PL/SQL Profile

All training is provided by LightSpeed PM

a Certified Quest Partner