Course Agenda

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

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

3. Working with Database Data

• SELECTing Single Rows
• Declaring Variable Datatypes Dynamically
• Modifying Database Data (DML)
• Transaction Control Statements

4. Selecting Multiple Rows Using Cursors

• Declaring Explicit Cursors
• Implicit Cursor Attributes
• Using the Cursor FOR LOOP

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

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

7. Introduction to Procedures and Functions

• Creating Stored PL/SQL Objects, Procedures, Functions

8. Creating Packages

• Creating Package Specifications and Bodies
• One Time Only Procedures
• Persistent State

9. Creating DML Triggers

• Triggering Events and Trigger Behavior
• Correlation Identifiers and Multi-statement Triggers
• Trigger Firing Behavior and Enabling/Disabling Triggers

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

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

13. Bulk-Bind Data Loading Using PL/SQL

• Defining Bulk Binds
• Error Handling with Bulk Binds

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

15. Writing Native Dynamic SQL

• EXECUTE IMMEDIATE

16. PL/SQL Wrapper

• PL/SQL Wrapper (source code encryption)

17. Understanding Dependencies

• Viewing Dependencies
• Effect of Breaking Dependency Chain

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

19. Objects

• Basic Objects
• Object Inheritance