Oracle Program with PL/SQL Training Course

Course Contents - DAY 1

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

Oracle Program with PL/SQL Training Course

Course Contents - DAY 2

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

Oracle Program with PL/SQL Training Course

Course Contents - DAY 3

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

Oracle Program with PL/SQL Training Course

Course Contents - DAY 4

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

Oracle Program with PL/SQL Training Course

Course Contents - DAY 5

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