Advanced PL/SQL

Course Number:  586-JK
Recommended Duration:  2 days

Intended Audience
Experienced PL/SQL developers who need to advance their PL/SQL skills.

Course Overview
Course appropriate for Oracle 11g and 10g.  Course can be taught with earlier versions of the database.  There are eight machine exercises.

Objectives
PL/SQL developers will learn to use advanced features enabling improved programming and enhanced performance.

Prerequisites
Working Oracle PL/SQL experience of at least six months or completion of “Oracle PL/SQL for Developers” (or equivalent) course.

Topics

• Review of PL/SQL

• Exception Handling overview

• Procedures, Functions, Packages

• Invoker Rights

• Autonomous Transactions

• Using Purity Features (WNDS, RNDS, RNPS, WNPS, TRUST)

• Using Overloading

• Dependencies

• Triggers

• SQL RETURNING Clause

• Oracle-Supplied Packages

• Fine-grained security: DBMS_RLS, DBMS_SESSION, others

• Implicit and Explicit Cursors

• Cursor variables, REF Cursors, parms

• Oracle Objects: Types & Subtypes

• Associative Arrays (PL/SQL tables)

• Nested Tables & VARRAYS

• Using External C & OS routines

• Create Java "Wrapper" to Java class

• Using Java classes from PL/SQL

• Performance and PL/SQL

• BULK COLLECT and FORALL

• NOCOPY hint

• PL/SQL Memory and SGA

• Pragma SERIALLY_REUSABLE

• "Pinning" PL/SQL

• Native Dynamic SQL

• Tracing & Profiling PL/SQL

• Wrapping PL/SQL Code