Oracle 9i Intermediate PL/SQL

This course has been superseded

Try searching for alternatives

Course Description

This course will cover the Oracle 9i PL/SQL programming language. You will be able to control data sharing and locking, develop an understanding of multi-user and concurrent transactions and develop triggers, procedures, functions and packages.
3 Days
Contact us for pricing
 

Prerequisites

Oracle 9i Foundations: SQL Basics & SQL *Plus or Equivalent experience
Oracle 9i Introduction To PL/SQL or Equivalent Experience.

Review of Introduction to PL/SQL

History of PL/SQL
Features and benefits of PL/SQL
Relationship of PL/SQL to SQL
PL/SQL development tools
Native Compilation
PL/SQL development tools
Native Compilation
PL/SQL anonymous block structure
Lexical units
Variable declarations
PL/SQL types and Records
SELECTing single rows
Declaring variable datatypes dynamically
Modifying database data (DML)
Transaction control statements
Declaring explicit cursors
Implicit cursor attributes
Using the cursor FOR LOOP
Exception Handling
Creating Procedures, Functions, Packages, and DML Triggers

Advanced Cursors

Cursor parameters
Taking advantage of a weak cursor variable
OPEN FOR
FETCH and CLOSE
Using the FOR UPDATE clause
Using PL/SQL collections and nested collections

Advanced Packages

Initialising variables
Module Overloading
Recursion
Purity levels
Using the "Persistent State" to advantage
One Time Only Procedures

Advanced Triggers

Trigger limitations
Mutating and Constraining Tables
Using CALL
Using client triggers
DDL Triggers
Using SERVERERROR event
Schema vs. Database triggers
Using alternative events and levels
INSTEAD OF triggers on views

PL/SQL Composite Datatypes and Collections

PL/SQL records
PL/SQL associative arrays
PL/SQL arrays of records
Using PL/SQL record variables
PL/SQL collections

Bulk-Bind Data Loading Using PL/SQL

Defining bulk binds
Error handling with bulk binds

Using Oracle Supplied Packages

DBMS_OUTPUT package
UTL_FILE package (file i/o)
DBMS_ALERT Package
DBMS_PIPE Package DBMS_JOB Package
DBMS_STATS Package
DBMS_UTILITY Package
UTL_SMTP Package
DBMS_SQL Package

Writing Native Dynamic SQL

PL/SQL Wrapper

PL/SQL wrapper (source code encryption
Let`s wrap

Understanding Dependencies

Viewing dependencies
Effect of breaking dependency chain

Large Object Management in PL/SQL

Differences between LONG/LONG RAW and LOBs
Creating and using BFILEs
Creating tables with LOBs
LOBs and PL/SQL
DBMS_LOB capabilities
Temporary LOBs

Objects

Basic Objects
Object Inheritance