Print Outline in PDF format

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.

You will cover…

• The PL/SQL Environment

• PL/SQL Program Structure

• Native Compilation

• Update, Insert and Delete Statements

• Variable Scope Rules

• Error Functions / Debugging

• Logical Comparisons

• Defining, Executing and Testing Procedures

• Defining, Executing and Testing Functions

• Creating Package Specifications And Bodies

• Creating Triggers Of All Types

• Using Oracle Supplied Packages

• Understanding Advanced PL/SQL Features

Who Needs to Attend:

Application Developers and Database Administrators who need a comprehensive understanding of Oracle 9i PL/SQL language.

Prerequisites:

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

Course Labs:

1. Creating Procedures

2. Creating Functions

3. Creating Packages

4. Creating Triggers

5. Embedded Functions and Procedures

6. Creating Autonomous Transactions

7. Encrypting Source Code

8. Using the UTL_FILE Package

9. Using the DBMS_ALERT Package

10. Creating Object Types

11. Creating and Manipulating Object Tables

12. Working With Collections

13. Collections And Bulk Binding

14. DBMS_SQL / Native Dynamic SQL

15. Working With Large Objects

16. Load, Publish And Run Java

Course Content:

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 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

• Initializing 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