ORACLE Training
Oracle PL/SQL Stored Program Units
Overview
The Oracle PL/SQL Stored Program Units course provides practical experience in developing and writing triggers, functions, procedures and packages. It also introduces some of the Oracle-supplied packages.
Prerequisites
Practical experience of Oracle SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses.
Objectives
To provide the skills needed to develop, write and maintain triggers, stored program units and packages.
Outline
Course Contents - DAY 1
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
Course Contents - DAY 2
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
Course Contents - DAY 3
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