Skip to main Content

Oracle PL/SQL Stored Program Units

  • Course Code OSPU
  • Duration 3 days

Additional Payment Options

  • GTC 22 inc. VAT

    GTC, Global Knowledge Training Credit, please contact Global Knowledge for more details

Public Classroom Price

£1,495.00

excl. VAT

Request Group Training Add to Cart

Course Delivery

This course is available in the following formats:

  • Company Event

    Event at company

  • Public Classroom

    Traditional Classroom Learning

  • Virtual Learning

    Learning that is virtual

Request this course in a different delivery format.

Course Overview

Top

Oracle PL/SQL Stored Program Units Course 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.

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

Versions supported 12cR2,18c and 19c.

Course Schedule

Top
    • Delivery Format: Virtual Learning
    • Date: 22-24 May, 2024
    • Location: Virtual

    £1,495.00

    • Delivery Format: Virtual Learning
    • Date: 24-26 July, 2024
    • Location: Virtual

    £1,495.00

    • Delivery Format: Virtual Learning
    • Date: 18-20 September, 2024
    • Location: Virtual

    £1,495.00

Target Audience

Top

Who will the Course Benefit?

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.

Course Objectives

Top

Course Objectives

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

Course Content

Top

Oracle PL/SQL Stored Program Units Training Course

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

Oracle PL/SQL Stored Program Units Training Course

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

Oracle PL/SQL Stored Program Units Training Course

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

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

Course Prerequisites

Top

Requirements

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.

Follow on Courses

Top

Further Learning

  • Oracle Advanced PL/SQL
  • Oracle Database 19c Administration
Cookie Control toggle icon