Oracle PL/SQL Stored Program Units

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: TRIGGERS

• DML Triggers
• The CREATE TRIGGER Statement
• Writing Trigger Code
• INSTEAD OF Triggers
• Calling Procedures from Triggers
• Coding Restrictions
• System Event and DDL Triggers
• Attribute Functions
• Compound Triggers
• Create Trigger Follows Clause
• Managing Triggers
• Privileges Required for Triggers
• Dictionary Information Concerning Triggers

Session 2: PROCEDURES

• What is a Procedure?
• The CREATE PROCEDURE Statement
• Procedure Parameters
• Invoking Procedures
• Local Subprograms
• Named Association Parameter Passing
• Definer's Rights and Invoker's Rights
• Autonomous Transactions
• Managing Procedures
• Privileges Required for Procedures
• Dictionary Information Concerning Procedures
• The Call Statement

Session 3: FUNCTIONS

• What is a Function?
• The CREATE FUNCTION Statement
• Executing Functions
• Invoker's Rights
• Autonomous Transactions
• DBMS_OUTPUT
• Using Functions in SQL Statements
• Deterministic and Parallel-Enabled Functions
• Function Result Cache
• Managing Functions
• Privileges Required for Functions
• Dictionary Information Concerning Functions

Session 4: PACKAGES

• What is a Package?
• Public and Private Components
• Creating a Package
• Example Package
• Persistent States
• One-time-only Procedures
• Overloading
• Purity Level Checking
• Forward Declarations
• Wrapping Packages
• Managing Packages
• Privileges Required for Packages
• Dictionary Information Concerning Packages

Session 5: USING PL/SQL RECORDS AND TABLES IN PACKAGES

• Overview of PL/SQL Records, Index-by Tables and Associative Arrays
• Using PL/SQL Records and Tables in Packages
• Table Built-in Functions
• The NOCOPY Hint
• Bulk Collection
• Bulk Binding DML Statements

Session 6: CURSOR VARIABLES (REF CURSORS)

• Declare Cursor Variables
• Use Cursor Variables
• Open and Close Cursor Variables
• Fetch Rows
• Cursor Variable Attributes
• Pass Cursor Variables as Parameters
• Refcursor Datatype

Session 7: MANAGING DEPENDENCIES

• Dependent and Referenced Objects
• Invalidation and Recompilation
• Local and Remote Dependencies
• Recompilation Considerations

Session 8: NATIVE COMPILATION AND COMPILE-TIME WARNINGS

• Introduction
• Native Compilation
• Automatic Recompilation
• Automatic Program Sublining
• WHEN OTHERS ...THEN NULL
• Data Dictionary Information
• Compiler Warning Categories
• Using the DBMS_WARNING Package

Session 9: ORACLE-SUPPLIED PACKAGES

• Overview of Oracle-Supplied Packages
• Using the DBMS_SQL Package
• Using Native Dynamic SQL
• The DBMS_METADATA Package
• The UTL_MAIL Package
• The DBMS_APPLICATION_INFO Package
• The DBMS_UTILITY Package
• Scheduling Jobs using the DBMS_SCHEDULER Package