About this Course

The course provides attendees with the background to develop stored PL/SQL programs including functions, procedures, packages, and triggers.

Commonly used predefined packages provided with the Oracle11g DBMS are examined. Discussions include how these packages could be used as part of in-house applications. Additional PL/SQL features such as native dynamic SQL support and autonomous transactions are also presented. Workshops provide students with the opportunity to use predefined packages and evaluate their potential role in an application as well as to write their own stored PL/SQL objects.

This course can be taught for any Oracle version.

Audience Profile

Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database.

Prerequisites

OR5460 Oracle PL/SQL Programming or equivalent experience.

Course Outline

1. PL/SQL on the Server

• Creating and Calling Stored Procedures
• Creating and Calling Stored Functions
• Packages
• Overloading Programs
• Passing Parameters
• Definer's versus Invoker's Rights
• Creating and Using Packages
• Cross-Section PL/SQL Function Result Cache
• Database Triggers
• Compound Triggers
• Viewing Source Code

2. Advanced Features

• PL/SQL Associative Arrays
• The VARRAY Type
• Nested Tables
• BULK COLLECT for Performance
• Autonomous Transactions
• Subprogram Inlining

3. Predefined Packages

• UTL_FILE
• DBMS_RESUMABLE
• DBMS_ALERT
• DBMS_FLASHBACK
• Other Packages