This course will cover the Oracle 10g PL/SQL programming language. You will learn to control data sharing and locking, develop an understanding of multi-user and concurrent transactions, and learn to develop triggers, procedures, functions, and packages.

What You'll Learn

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

• Oracle 10g Foundations: SQL & SQL*Plus (OR103)

Follow-On Courses

There are no follow-ons for this course.

Course Outline

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

2. 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
• PL/SQL Style Guide and Coding Conventions

3. Working with Database Data

• SELECTing Single Rows
• Declaring Variable Datatypes Dynamically
• Modifying Database Data (DML)
• Transaction Control Statements

4. Selecting Multiple Rows Using Cursors

• Declaring Explicit Cursors
• Implicit Cursor Attributes
• Using the Cursor FOR LOOP

5. Exception Handling

• Writing an Exception Handler Section
• Handling Predefined Exceptions
• Controlling Exception Processing - Exception Propagation
• RAISE_APPLICATON_ERROR Use
• Preventing Unhandled Exceptions
• Exception Propagation
• Using PRAGMA EXCEPTION_INIT

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

7. Introduction to Procedures and Functions

• Creating Stored PL/SQL Objects, Procedures, Functions

8. Creating Packages

• Creating Package Specifications and Bodies
• One Time Only Procedures
• Persistent State

9. Creating DML Triggers

• Triggering Events
• Trigger Behavior
• Correlation Identifiers
• Multi-Statement Triggers
• Trigger Firing Behavior
• Enabling/Disabling Triggers

10. Advanced Packages

• Initializing Variables
• Module Overloading
• Recursion
• Purity Levels

11. Advanced Triggers

• Trigger Limitations
• Mutating and Constraining Tables
• Using CALL
• 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

12. Bulk-Bind Data Loading Using PL/SQL

• Defining Bulk Binds
• Error Handling with Bulk Binds

13. Using Oracle Supplied Packages

• DBMS_OUTPUT Package
• UTL_FILE Package (file i/o)
• DBMS_ALERT/PIPE Packages
• DBMS_JOB Package
• DBMS_SCHEDULER Package
• DBMS_STATS Package
• DBMS_UTILITY Package
• UTL_SMTP/MAIL Packages
• DBMS_SQL Package

14. Writing Native Dynamic SQL

• EXECUTE IMMEDIATE

15. PL/SQL Wrapper

• PL/SQL Wrapper (source code encryption)

16. Dependencies

• Viewing Dependencies
• Effect of Breaking Dependency Chain

17. Large Object Management in PL/SQL

• LONG/LONG RAW vs. LOBs
• Creating and using BFILEs
• Tables with LOBs
• LOBs and PL/SQL
• DBMS_LOB Capabilities
• Temporary LOBs

18. Objects

• Basic Objects
• Object Inheritance

19. Java in PL/SQL

Labs

Lab 1: Using PL/SQL to Create an Anonymous Block

Lab 2: PL/SQL Program Control

Lab 3: Selecting and Updating Database Data

Lab 4: Using Explicit Cursors

Lab 5: Handling Exceptions

Lab 6: Creating Procedures

Lab 7: Creating Functions

Lab 8: Creating Packages

Lab 9: Creating Triggers

Lab 10: Embedded Functions and Procedures

Lab 11: Creating Autonomous Transactions

Lab 12: Encrypting Source Code

Lab 13: Using the UTL_FILE Package

Lab 14: Using the DBMS_ALERT Package

Lab 15: Creating Object Types

Lab 16: Creating and Manipulating Object Tables

Lab 17: Working with Collections

Lab 18: Collections and Bulk Binding

Lab 19: DBMS_SQL / Native Dynamic SQL

Lab 20: Working with Large Objects

Lab 21: Load, Publish, and Run Java