PL/SQL Stored Program Units

Prerequisites:

Attendance on SQL*Plus (OSP) and PL/SQL (OPI).

Course Description:

This course provides practical experience in developing and writing triggers, functions, procedures and packages.It also introduces some of the Oracle-supplied packages. It will benefit Oracle database administrators and software development personnel who need to write new or maintain existing, PL/SQL triggers, program units and packages.

The delegates will practise:

• Implementing various types of trigger
• Creating, managing and invoking stored procedures
• Creating, managing and executing stored functions
• Developing and using packages
• Using package features and constructs effectively
• Wrapping packages
• Using Cursor Variables
• Using records and tables or associative arrays in packages
• Implementing Bulk Binding and Bulk Collecting techniques
• Managing program unit dependencies
• Implementing Native Compilation and Compile-time Warnings
• Implementing Native Dynamic SQL
• Using a variety of Oracle-supplied packages

This course includes the following modules:

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

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

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

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

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

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

Managing Dependencies

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

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

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