Course Content
PL/SQL fundamentals
- Declaring and anchoring variables to database definitions
- Flow control constructs
Oracle 11g and 12c PL/SQL features
- PL/Scope in Oracle 11g
- Returning implicit cursor results from stored procedures in Oracle 12c
- Declaring local functions within SELECT statements in Oracle 12c
Data Manipulation Techniques
Maintaining data with DML statements
- Employing the RETURNING INTO clause
- Solving the fetch-across-commit problem
Managing data retrieval with cursors
- Implications of explicit and implicit cursors
- Simplifying cursor processing with cursor FOR LOOPs
- Embedding cursor expressions in SELECT statements
Cursor variables
- Strong vs. weak cursor variables
- Passing cursor variables to other programs
- Defining REF CURSORS in packages
Developing Well-Structured and Error-Free Code
Error handling using EXCEPTIONs
- Propagation and scope
- "Retrying" problem transactions with EXCEPTION processing
Debugging PL/SQL blocks
- Simplifying testing and debugging with conditional compilation
- Interpreting compiler messages
- Applying structured testing techniques
- Building and applying a test bed
- Leveraging the debugging facilities in SQL Developer
Achieving Maximum Reusability
Writing stored procedures and functions
- Calling and invoking server-side logic
- Passing input and output parameters
- Implementing an autonomous transaction
Coding user-written functions
- Calling PL/SQL functions from SQL
- Building table-valued functions
Developing safe triggers
- Employing :OLD and :NEW variables
- Avoiding unreliable trigger constructs
- Exploiting schema and database triggers
Exploiting Complex Datatypes
Collection types
- PL/SQL tables, nested tables, VARRAYs
- Stepping through dense and nonconsecutive collections
Bulk binding for high performance
- Moving data into and out of PL/SQL blocks
- BULK COLLECT INTO and FORALL
- BULK cursor attributes
- BULK EXCEPTION handling
Invoking Native Dynamic SQL
Finessing the compiler
- The EXECUTE IMMEDIATE statement
- The RETURNING INTO clause
Types of dynamic SQL
- Building SQL statements during runtime
- Autogenerating standard code
Package Tips and Techniques
- Package structure: SPEC and BODY
- Eliminating dependency problems
- Overloading for polymorphic effects
- Evaluating application frameworks
- Declaring and using persistent global variables
