Course Description

The Oracle Program with PL/SQL training course introduces the delegate to Oracle's PL/SQL programming language and provides practical experience in writing stand-alone programs, developing database triggers and implementing stored 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, software development personnel and database support staff who need to write PL/SQL scripts and implement or maintain database triggers, program units and packages.

Pre-requisites

A working knowledge of Oracle SQL or SQL Developer is required along with practical experience in writing SQL statements. This knowledge can be obtained by attendance on the pre-requisite Oracle SQL course. Some previous programming experience will also prove advantageous.

Course Objectives

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

Course Content

Course Introduction

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

Session 1: PL/SQL FUNDAMENTALS

• What is PL/SQL?
• Basic Elements
• Variables and Constants
• Data Types
• Initializing Variables and Assigning Values
• Using SQL Statements in Code
• Generating Output to SQL or SQL Developer

Session 2: PROGRAM LOGIC

• IF THEN ELSE Statements
• CASE Expressions
• The Basic Loop Construct
• WHILE and FOR Loops
• Nested and Labelled Loops
• The GOTO Statement
• The CONTINUE Statement

Session 3: USING CURSORS

• What is a Cursor?
• Implicit and Explicit Cursors
• Cursor Operations
• Declaring,Opening and Closing Cursors
• Fetching Rows
• Status Checking
• Using Cursors FOR UPDATE
• The Cursor FOR Loop
• Parameterised Cursors

Session 4: EXCEPTIONS AND NESTED BLOCKS

• The EXCEPTION Section
• Types of Exception
• Handling Named System-Raised Exceptions
• Handling Unnamed System-Raised Exceptions
• User-Declared Exceptions and Application Errors
• When others then Null
• Nested and Labelled Blocks
• Propagation of Exceptions
• Scope of Variables and Cursors

Session 5: PL/SQL RECORDS AND INDEX-BY TABLES

• Declaring Record Types
• Handling PL/SQL Records
• Nested Records
• Declaring PL/SQL Index-By Tables or Associative Arrays
• PL/SQL Table Built In Functions
• Manipulating PL/SQL Tables or Associative Arrays

Session 6: 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 7: PROCEDURES

• What is a Procedure?
• The CREATE PROCEDURE Statement
• Procedure Parameters
• Invoking Procedures
• Local Subprograms
• Named Association Parameter Passing
• Definer's Right and Invoker's Rights
• Autonomous Transactions
• Managing Procedures
• Privileges Required for Procedures
• Dictionary Information Concerning Procedures
• The Call Statement

Session 8: 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 9: 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 Features

Session 10: 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 11: 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
• Open and Close Cursor Variables
• Refcursor Datatype

Session 12: MANAGING DEPENDENCIES

• Dependent and Referenced Objects
• Invalidation and Recompilation
• Local and Remote Dependencies
• Recompilation Considerations

Session 13: 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 14: 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 15: 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