Course Icon ORACLE Training

Oracle PL/SQL Stored Program Units

0800 1337 401

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
See all of the available Technical Courses