Advanced PL/SQL
- Recommended Duration:
- 2 Days
- Version: Oracle 11g and 10g
- Course can be taught with earlier versions of the database.
- Benefits
- PL/SQL developers will learn to use advanced features enabling improved programming and enhanced performance.
- Audience
- Experienced PL/SQL developers who need to advance their PL/SQL skills.
- Prerequisites
- Working Oracle PL/SQL experience of at least six months or completion of “Oracle PL/SQL for Developers” (or equivalent) course.
- Exercises
- There are eight machine exercises.
- Major 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
- "Pinning" PL/SQL
- Native Dynamic SQL
- Tracing & Profiling PL/SQL
- Wrapping PL/SQL Code