Review Of Introduction to PL/SQL

• Features and benefits of PL/SQL
• Relationship of PL/SQL to SQL
• PL/SQL development tools
• Native Compilation
• PL/SQL System/Session Params
• PL/SQL anonymous block Variable declarations
• PL/SQL types and Records
• Declaring variable datatypes dynamically
• Modifying database data (DML)
• Transaction control statements
• Declaring explicit cursors
• Implicit cursor attributes
• Exception Handling
• Creating Procedures, Functions, Packages, and DML Triggers
• Debugging With SQL Developer
• Conditional Compilation
• Warnings

Advanced Cursors

• Cursor parameters
• Parameter defaults
• Taking advantage of a weak cursor variable
• OPEN FOR, FETCH and CLOSE
• Using the FOR UPDATE clause
• Using PL/SQL collections and nested collections

Advanced Packages

• Initializing variables
• Module Overloading
• Recursion
• Purity levels
• Using the ?Persistent State? to advantage
• One Time Only Procedures
• Forward Declarations
• Using Persistent State
• Code Encapsulation
• Constant and Exception Standardization

Advanced Triggers

• Trigger limitations
• Mutating and Constraining Tables
• Using CALL and client triggers
• DDL / SERVERERROR triggers
• LOGON/LOGOFF, SUSPEND, STARTUP/SHUTDOWN triggers
• Schema vs. Database triggers
• Using alternative events and levels
• INSTEAD OF triggers on views

PL/SQL Composite Datatypes and Collections

• PL/SQL records
• Associative Arrays (INDEX BY)
• Nested Tables
• VARRAYs
• Built-in type methods
• Arrays of composite types
• Using PL/SQL record variables
• PL/SQL collections

Bulk-Bind Data Loading Using PL/SQL

• Defining bulk binds
• BULK COLLECT / FORALL
• Error handling with bulk binds ? SAVE EXCEPTIONS

Using Oracle Supplied Packages

• DBMS_OUTPUT Package
• UTL_FILE Package (file i/o)
• DBMS_ALERT Package
• DBMS_PIPE Package
• DBMS_JOB Package
• DBMS_SCHEDULER Package
• DBMS_STATS Package
• DBMS_UTILITY Package
• UTL_SMTP Package
• UTL_MAIL Package
• DBMS_SQL Package

Writing Native Dynamic SQL

• EXECUTE IMMEDIATE
• Benefits Of NDS

PL/SQL Wrapper

• PL/SQL wrapper (source code encryption)

Understanding Dependencies

• Defining dependencies
• Local vs. Remote
• Viewing dependencies
• Effect of breaking dependency chain

Large Object Management in PL/SQL

• Differences between LONG/LONG RAW and LOBs
• Creating and using BFILEs
• Creating tables with LOBs
• LOBs and PL/SQL
• DBMS_LOB capabilities
• Temporary LOBs

Objects

• Basic Objects
• Object Inheritance

Java

• Java in PL/SQL
• Sample encapsulation