Oracle PL-SQL Stored Program Units

Course Overview

Course Prerequisites

• • Prerequisite Courses : Oracle SQL, Oracle PL/SQL
  • 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.

Target Audience for this Course

• • 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 SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language are required.

Skills Gained from this Training

• • 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

Included with this course

• • Comprehensive course materials and exercises

Course Content

• DAY 1

  
  

• Course Introduction

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

• 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

• DAY 2

  
  

• 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

• DAY 3

  NULL

• 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

• Note

  The 3 Day Stored Program Units course can be combined with the 2 Day Oracle PL/SQL course, this is equivalent to attending the 5 Day Oracle Program with PL/SQL course.