Oracle 11g PL/SQL Intermediate

1. 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

2. 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

3. Advanced Packages

• Initializing variables
• Module overloading
• Recursion
• Purity levels
• Using the "Persistent Stateto advantage
• One time only procedures
• Forward declarations
• Using Persistent State
• Code encapsulation
• Constant and exception standardization

4. 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

5. 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

6. Bulk-Bind Data Loading Using PL/SQL

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

7. 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

8. Writing Native Dynamic SQL

• EXECUTE IMMEDIATE
• Benefits of NDS

9. PL/SQL Wrapper

• PL/SQL wrapper (source code encryption)

10. Understanding Dependencies

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

11. 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

12. Objects

• Basic objects
• Object inheritance

13. Java

• Java in PL/SQL
• Sample encapsulation

• PL/SQL Environment
• PL/SQL Program Structure
• Native Compilation
• Update, Insert, and Delete Statements
• Error Functions/Debugging
• Defining, Executing, and Testing Functions and Procedures
• Creating Package Specifications and Bodies
• Creating Triggers of All Types
• Using Oracle-Supplied Packages
• Advanced PL/SQL Features

Lab 1: Creating Procedures

Lab 2: Creating Functions

Lab 3: Creating Packages

Lab 4: Creating Triggers

Lab 5: Embedded Functions and Procedures

Lab 6: Creating Autonomous Transactions

Lab 7: Encrypting Source Code

Lab 8: Using the UTL_FILE Package

Lab 9: Using the DBMS_ALERT Package

Lab 10: Creating Object Types

Lab 11: Creating and Manipulating Object Tables

Lab 12: Working with Collections

Lab 13: Collections And Bulk Binding

Lab 14: DBMS_SQL/Native Dynamic SQL

Lab 15: Working with Large Objects

Lab 16: Load, Publish, and Run Java

• Oracle 11g SQL Basics & SQL*PLUS or equivalent experience
• Oracle 11g PL/SQL Introduction or equivalent experience

Application developers and database administrators who need a comprehensive understanding of Oracle 10g PL/SQL language