Oracle 11g Advanced PL/SQL

Course:  ORPLSA
Duration:  3 Days
Level:  III
Course Summary

This class covers advanced topics related to Oracle PL/SQL. This class provides the technical expertise necessary to utilize these powerful components of Oracle.

Attention in this class is given to some aspects of PL/SQL that are often not clearly understood (collections and overloading) or fully utilized (default values for program units, package startup code) along with advanced topics such as autonomous transactions, REF cursors and bulk binding. Some of the newer features of PL/SQL (the MERGE and CASE statements) are introduced. Techniques for measuring PL/SQL performance and features to enhance the performance of PL/SQL are also presented.

The functionality of a number of highly leverable Oracle supplied PL/SQL packages is covered.

This class can be conducted over a span of 2 or 3 days. See the Course Topics sections for the exact contents of each class duration.

« Hide The Details
Topics Covered In This Course

Oracle's SQL tools

  • SQL*Plus
  • iSQL*Plus
  • SQL*Developer

New Oracle features

  • Boolean variables
  • CASE statement
  • MERGE statement
  • Timestamp datatype
  • Interval datatype
  • Related functions
  • CAST function
  • Regular expressions

Working with block hierarchies

  • Understanding block hierarchies
  • Variable scope in hiararchies
  • Using block labels
  • Referencing out-of-scope variables
  • Navigating block hierarchies
  • The (dreaded) GOTO statement

Advanced exception handling

  • Capturing unexpected exceptions
  • Error trapping functions
  • User defined exceptions
  • Overriding an exception
  • Escalate an exception
  • Continuing after an exception

Maintaining PL/SQL stored program units

  • Using the Oracle data dictionary
  • Compiling, recompiling, dropping program units
  • Understanding object dependencies

Advanced features of packages

  • Overloading
  • Forward referencing
  • Package start up code

Advanced features of triggers

  • DML trigger directives
  • Autonomous triggers
  • INSTEAD OF triggers

Advanced cursor topics

  • Cursor parameters
  • Embedded cursors
  • Cursor variables (REF cursors)
  • Using anticipatory locking in cursors

Oracle supplied packages


PL/SQL composite variables

  • Records
  • Working with collections
  • Associative arrays
  • Nested tables

Dynamic SQL

  • Native dynamic SQL
  • Using DBMS_SQL

Working with large objects (LOBs)

  • Understanding LOBs
  • Creating LOBs
  • Manipulating LOBs with DBMS_LOB
  • LOBs and NULL values

PL/SQL performance tuning

  • Using DBMS_TRACE
  • NOCOPY hint

Advanced compilation techniques

  • Native compilation
  • Controlling compile error reporting
  • Conditional compilation
Who Should Take This Course

Intermediate Oracle PL/SQL developers and DBAs.

Recommended Prerequisites

Solid understanding of PL/SQL, including the use of program units (procedures, functions, packages and triggers) is required to succeed in this class.

Training Style

Lecture with frequent hands on labs and several multi-group projects.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle 11g/12c Developer: Introduction to PL/SQL
2 Days
Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL
5 Days
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
Oracle APEX Version 5.0.4
5 Days
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
Oracle 11g New and Advanced SQL Features For Developers
3 Days

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]