Course Description

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.

Similarly, the course is targeted to closely follow the official Oracle Database curriculum for certification.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Versions supported 12cR2, 18c and 19c.

Target Student

Oracle database administrators and software development personnel who need to write new or maintain existing, PL/SQL triggers, program units and packages.

Practical experience of Oracle SQL and of using SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language are required.

Pre-requisites

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.

Course Objectives

To provide the skills needed to develop, write and maintain triggers, stored program units and packages.

Course Content

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

Session 10: PL/SQL DESIGN CONSIDERATIONS

• Invoker versus Definer™ Rights
• Grant Roles to PL/SQL Packages and Programs
• Programming Standards for Variables,Parameters and Constants
• Standardise Constants with a Package
• Standardise Exceptions with a Package
• Write PL/SQL Code using Local Subprograms
• Use NOCOPY Compiler Hint
• Optimise Code with the PARALLEL ENABLE hint
• Use the AUTONOMOUS TRANSACTION Pragma