• 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 and Functions

• Creating Package Specifications And Bodies, and 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 10g PL/SQL language.

Prerequisites:

Oracle 10g Foundation: SQL Basics & SQL*PLUS or Equivalent experience

Course Labs:

1. Using PL/SQL to create an Anonymous Block

2. PL/SQL Program Control

3. Selecting and Updating Database Data

4. Using Explicit Cursors

5. Handling Exceptions

6. Creating Procedures

7. Creating Functions

8. Creating Packages

9. Creating Triggers

10. Embedded Functions and Procedures

11. Creating Autonomous Transactions

12. Encrypting Source Code

13. Using the UTL_FILE Package

14. Using the DBMS_ALERT Package

15. Creating Object Types

16. Creating Manipulating Object Tables

17. Working With Collections

18. Collections And Bulk Binding

19. DBMS_SQL / Native Dynamic SQL

20. Working With Large Objects

21. Load, Publish And Run Java

Course Content:

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 style 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 the cursor FOR LOOP

Exception Handling

• Writing an exception handler section

• Handling predefined exceptions, Controlling exception processing – exception propagation

• Using RAISE_APPLICATON_ERROR

• Preventing unhandled exceptions

• Exception propagation

• 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

• Initializing variables, Module Overloading, Recursion, Purity levels, Using the “Persistent State” to advantage, and One Time Only Procedures

Advanced Triggers

• Trigger limitations, 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

• 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