Introduction to PL/SQL

• History of PL/SQL
• Features and benefits of PL/SQL
• Relationship of PL/SQL to SQL
• PL/SQL development tools
• Native Compilation

PL/SQL Basics

• PL/SQL anonymous block structure, Lexical units, Variable declarations, types, and Records
• SQL*Plus development environment
• Displaying messages with DBMS_OUTPUT
• Object naming rules, and PL/SQL style guide and coding conventions

Working with Database Data

• SELECTing single rows
• Declaring variable datatypes dynamically
• Modifying database data (DML)
• Transaction control statements

Selecting Multiple Rows Using Cursors

• Declaring explicit cursors
• Implicit cursor attributes
• Using the cursor FOR LOOP

Exception Handling

• Writing an exception handler section
• Handling predefined exceptions, Controlling exception processing ? exception propagation
• RAISE_APPLICATON_ERROR use
• Preventing unhandled exceptions
• Exception propagation
• Using PRAGMA EXCEPTION_INIT

Advanced Cursors

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

Introduction to Procedures and Functions

• Creating stored PL/SQL objects, procedures, functions

Creating Packages

• Creating package specifications and bodies
• One Time Only Procedures
• Persistent State

Creating DML Triggers

• Triggering events, Trigger behavior
• Correlation identifiers, Multi-statement triggers
• Trigger firing behavior, Enabling/Disabling triggers

Advanced Packages

• Initializing variables
• Module Overloading
• Recursion
• Purity levels

Advanced Triggers

• Trigger limitations, Mutating and Constraining Tables
• Using CALL, and client triggers
• DDL Triggers
• Using SERVERERROR event
• Schema vs. Database triggers
• Using alternative events and levels
• INSTEAD OF triggers on views

PL/SQL Composite Datatypes and Collections

• PL/SQL records, PL/SQL associative arrays, and arrays of records
• Using PL/SQL record variables
• PL/SQL collections

Bulk-Bind Data Loading Using PL/SQL

• Defining bulk binds
• Error handling with bulk binds

Using Oracle Supplied Packages

• DBMS_OUTPUT package
• UTL_FILE package (file i/o)
• DBMS_ALERT/PIPE Packages
• DBMS_JOB Package
• DBMS_SCHEDULER Package
• DBMS_STATS Package
• DBMS_UTILITY Package
• UTL_SMTP/MAIL Packages
• DBMS_SQL Package

Writing Native Dynamic SQL

• EXECUTE IMMEDIATE

PL/SQL Wrapper

• PL/SQL wrapper (source code encryption)

Understanding Dependencies

• Viewing dependencies
• Effect of breaking dependency chain

Large Object Management in PL/SQL

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

Objects

• Basic Objects, Object Inheritance

Java in PL/SQL