Oracle 9i PL/SQL Foundations

This course has been superseded

We suggest the following instead:
Oracle Database: PL/SQL I Introduction

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, function and packages.

View other Oracle Courses available
5 Days
Contact us for pricing
 

Prerequisites

Oracle 9i Foundation: SQL Basics & SQL *Plus or equivalent experience.

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 Basics

PL/SQL anonymous block structure, Lexical units, Variable declarations, types, and Records
SQL *Plus development environment
Displaying messages with DBMS_OUTPUT
Object naming rules, and PL/SQL stype guide and coding conventions

Working with Database Data

SELECTing single rows
Declaring variable datatypes dynamically
Modifying database data (DML)
Transaction control statements

Selecting Multiple Rows Using Cursors

Declaring explicit cursors
Implicit cursor attributes
Using teh cursor FOR LOOP

Exception Handling

Writing an exception handler section
Handling predefined exceptions, Controlling exception processing - exception propagation
Using RAISE_APPLICATOIN_ERROR
Preventing unhandled exceptions
Exception propagations
Using PRAGMA EXCEPTION_INIT

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

Introduction to Procedures and Functions

Creating stored PL/SQL objects, procedures, functions

Creating Packages

Creating package specifications and bodies

Creating DML Triggers

Triggering events, Trigger behavior
Correlation identifiers, Multi-statement triggers
Trigger firing behavior, Enabling/Disabling triggers

Advanced Packages

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

Advanced Triggers

Triggerlimitations, Mutating and Constraining Tables
Using CALL, and 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, and 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
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, and tables with LOBs
LOBs and PL/SQL
DBMS_LOB capabilities
Temporary LOBs

Objects

Basic Objects, Object Inheritance

OracleOracle 9iOracle Database 9i