Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience
Documentation Accessibility
Structure
PL/SQL Sample Programs
Related Documents
Conventions

What's New in PL/SQL?

New Features in PL/SQL for Oracle Database 10g Release 2 (10.2)
New Features in PL/SQL for Oracle Database 10g Release 1 (10.1)

1 Overview of PL/SQL

Advantages of PL/SQL
Tight Integration with SQL
Better Performance
Higher Productivity
Full Portability
Tight Security
Access to Pre-defined Packages
Support for Object-Oriented Programming
Support for Developing Web Applications and Pages
Understanding the Main Features of PL/SQL
Understanding PL/SQL Block Structure
Understanding PL/SQL Variables and Constants
Declaring Variables
Assigning Values to a Variable
Bind Variables
Declaring Constants
Processing Queries with PL/SQL
Declaring PL/SQL Subprograms
Declaring Datatypes for PL/SQL Variables
%TYPE
%ROWTYPE
Understanding PL/SQL Control Structures
Conditional Control
Iterative Control
Sequential Control
Understanding Conditional Compilation
Writing Reusable PL/SQL Code
Subprograms: Procedures and Functions
Packages: APIs Written in PL/SQL
Inputting and Outputting Data with PL/SQL
Understanding PL/SQL Data Abstraction
Cursors
Collections
Records
Object Types
Understanding PL/SQL Error Handling
PL/SQL Architecture
In the Oracle Database Server
Anonymous Blocks
Stored Subprograms
Database Triggers
In Oracle Tools

2 Fundamentals of the PL/SQL Language

Character Sets and Lexical Units
Delimiters
Identifiers
Reserved Words
Predefined Identifiers
Quoted Identifiers
Literals
Numeric Literals
Character Literals
String Literals
BOOLEAN Literals
Datetime Literals
Comments
Single-Line Comments
Multi-line Comments
Restrictions on Comments
Declarations
Constants
Using DEFAULT
Using NOT NULL
Using the %TYPE Attribute
Using the %ROWTYPE Attribute
Aggregate Assignment
Using Aliases
Restrictions on Declarations
PL/SQL Naming Conventions
Scope and Visibility of PL/SQL Identifiers
Assigning Values to Variables
Assigning BOOLEAN Values
Assigning a SQL Query Result to a PL/SQL Variable
PL/SQL Expressions and Comparisons
Logical Operators
Order of Evaluation
Short-Circuit Evaluation
Comparison Operators
Relational Operators
IS NULL Operator
LIKE Operator
BETWEEN Operator
IN Operator
Concatenation Operator
BOOLEAN Expressions
BOOLEAN Arithmetic Expressions
BOOLEAN Character Expressions
BOOLEAN Date Expressions
Guidelines for PL/SQL BOOLEAN Expressions
CASE Expressions
Simple CASE expression
Searched CASE Expression
Handling Null Values in Comparisons and Conditional Statements
NULLs and the NOT Operator
Conditional Compilation
How Does Conditional Compilation Work?
Conditional Compilation Control Tokens
Using Conditional Compilation Selection Directives
Using Conditional Compilation Error Directives
Using Conditional Compilation Inquiry Directives
Using Predefined Inquiry Directives With Conditional Compilation
Using Static Expressions with Conditional Compilation
Setting the PLSQL_CCFLAGS Initialization Parameter
Using DBMS_DB_VERSION Package Constants
Conditional Compilation Examples
Using Conditional Compilation to Specify Code for Database Versions
Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
Conditional Compilation Restrictions
Using PL/SQL to Create Web Applications and Server Pages
PL/SQL Web Applications
PL/SQL Server Pages
Summary of PL/SQL Built-In Functions

3 PL/SQL Datatypes

Overview of Predefined PL/SQL Datatypes
PL/SQL Number Types
BINARY_INTEGER Datatype
BINARY_FLOAT and BINARY_DOUBLE Datatypes
NUMBER Datatype
PLS_INTEGER Datatype
PL/SQL Character and String Types
CHAR Datatype
LONG and LONG RAW Datatypes
RAW Datatype
ROWID and UROWID Datatype
VARCHAR2 Datatype
PL/SQL National Character Types
Comparing UTF8 and AL16UTF16 Encodings
NCHAR Datatype
NVARCHAR2 Datatype
PL/SQL LOB Types
BFILE Datatype
BLOB Datatype
CLOB Datatype
NCLOB Datatype
PL/SQL Boolean Types
BOOLEAN Datatype
PL/SQL Date, Time, and Interval Types
DATE Datatype
TIMESTAMP Datatype
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE Datatype
INTERVAL YEAR TO MONTH Datatype
INTERVAL DAY TO SECOND Datatype
Datetime and Interval Arithmetic
Avoiding Truncation Problems Using Date and Time Subtypes
Overview of PL/SQL Subtypes
Defining Subtypes
Using Subtypes
Type Compatibility With Subtypes
Constraints and Default Values With Subtypes
Converting PL/SQL Datatypes
Explicit Conversion
Implicit Conversion
Choosing Between Implicit and Explicit Conversion
DATE Values
RAW and LONG RAW Values
Differences between the CHAR and VARCHAR2 Datatypes
Assigning Character Values
Comparing Character Values
Inserting Character Values
Selecting Character Values

4 Using PL/SQL Control Structures

Overview of PL/SQL Control Structures
Testing Conditions: IF and CASE Statements
Using the IF-THEN Statement
Using the IF-THEN-ELSE Statement
Using the IF-THEN-ELSIF Statement
Using CASE Statements
Searched CASE Statement
Guidelines for PL/SQL Conditional Statements
Controlling Loop Iterations: LOOP and EXIT Statements
Using the LOOP Statement
Using the EXIT Statement
Using the EXIT-WHEN Statement
Labeling a PL/SQL Loop
Using the WHILE-LOOP Statement
Using the FOR-LOOP Statement
How PL/SQL Loops Iterate
Dynamic Ranges for Loop Bounds
Scope of the Loop Counter Variable
Using the EXIT Statement in a FOR Loop
Sequential Control: GOTO and NULL Statements
Using the GOTO Statement
Restrictions on the GOTO Statement
Using the NULL Statement

5 Using PL/SQL Collections and Records

What are PL/SQL Collections and Records?
Understanding PL/SQL Collections
Understanding Nested Tables
Understanding Varrays
Understanding Associative Arrays (Index-By Tables)
How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays
Understanding PL/SQL Records
Choosing Which PL/SQL Collection Types to Use
Choosing Between Nested Tables and Associative Arrays
Choosing Between Nested Tables and Varrays
Defining Collection Types and Declaring Collection Variables
Declaring PL/SQL Collection Variables
Initializing and Referencing Collections
Referencing Collection Elements
Assigning Collections
Comparing Collections
Using Multilevel Collections
Using Collection Methods
Checking If a Collection Element Exists (EXISTS Method)
Counting the Elements in a Collection (COUNT Method)
Checking the Maximum Size of a Collection (LIMIT Method)
Finding the First or Last Collection Element (FIRST and LAST Methods)
Looping Through Collection Elements (PRIOR and NEXT Methods)
Increasing the Size of a Collection (EXTEND Method)
Decreasing the Size of a Collection (TRIM Method)
Deleting Collection Elements (DELETE Method)
Applying Methods to Collection Parameters
Avoiding Collection Exceptions
Defining and Declaring Records
Using Records as Procedure Parameters and Function Return Values
Assigning Values to Records
Comparing Records
Inserting PL/SQL Records into the Database
Updating the Database with PL/SQL Record Values
Restrictions on Record Inserts and Updates
Querying Data into Collections of Records

6 Performing SQL Operations from PL/SQL

Overview of SQL Support in PL/SQL
Data Manipulation
Transaction Control
SQL Functions
SQL Pseudocolumns
SQL Operators
Managing Cursors in PL/SQL
Implicit Cursors
Attributes of Implicit Cursors
Guidelines for Using Attributes of Implicit Cursors
Explicit Cursors
Declaring a Cursor
Opening a Cursor
Fetching with a Cursor
Fetching Bulk Data with a Cursor
Closing a Cursor
Attributes of Explicit Cursors
Querying Data with PL/SQL
Selecting At Most One Row: SELECT INTO Statement
Selecting Multiple Rows: BULK COLLECT Clause
Looping Through Multiple Rows: Cursor FOR Loop
Performing Complicated Query Processing: Explicit Cursors
Querying Data with PL/SQL: Implicit Cursor FOR Loop
Querying Data with PL/SQL: Explicit Cursor FOR Loops
Defining Aliases for Expression Values in a Cursor FOR Loop
Using Subqueries
Using Correlated Subqueries
Writing Maintainable PL/SQL Queries
Using Cursor Variables (REF CURSORs)
What Are Cursor Variables (REF CURSORs)?
Why Use Cursor Variables?
Declaring REF CURSOR Types and Cursor Variables
Passing Cursor Variables As Parameters
Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE
Opening a Cursor Variable
Using a Cursor Variable as a Host Variable
Fetching from a Cursor Variable
Closing a Cursor Variable
Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL
Avoiding Errors with Cursor Variables
Restrictions on Cursor Variables
Using Cursor Expressions
Restrictions on Cursor Expressions
Example of Cursor Expressions
Constructing REF CURSORs with Cursor Subqueries
Overview of Transaction Processing in PL/SQL
Using COMMIT in PL/SQL
Using ROLLBACK in PL/SQL
Using SAVEPOINT in PL/SQL
How Oracle Does Implicit Rollbacks
Ending Transactions
Setting Transaction Properties with SET TRANSACTION
Restrictions on SET TRANSACTION
Overriding Default Locking
Doing Independent Units of Work with Autonomous Transactions
Advantages of Autonomous Transactions
Defining Autonomous Transactions
Comparison of Autonomous Transactions and Nested Transactions
Transaction Context
Transaction Visibility
Controlling Autonomous Transactions
Using Autonomous Triggers
Calling Autonomous Functions from SQL

7 Performing SQL Operations with Native Dynamic SQL

Why Use Dynamic SQL with PL/SQL?
Using the EXECUTE IMMEDIATE Statement in PL/SQL
Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings
Using Bulk Dynamic SQL in PL/SQL
Using Dynamic SQL with Bulk SQL
Examples of Dynamic Bulk Binds
Guidelines for Using Dynamic SQL with PL/SQL
Building a Dynamic Query with Dynamic SQL
When to Use or Omit the Semicolon with Dynamic SQL
Improving Performance of Dynamic SQL with Bind Variables
Passing Schema Object Names As Parameters
Using Duplicate Placeholders with Dynamic SQL
Using Cursor Attributes with Dynamic SQL
Passing Nulls to Dynamic SQL
Using Database Links with Dynamic SQL
Using Invoker Rights with Dynamic SQL
Using Pragma RESTRICT_REFERENCES with Dynamic SQL
Avoiding Deadlocks with Dynamic SQL
Backward Compatibility of the USING Clause
Using Dynamic SQL With PL/SQL Records and Collections

8 Using PL/SQL Subprograms

What Are Subprograms?
Advantages of PL/SQL Subprograms
Understanding PL/SQL Procedures
Understanding PL/SQL Functions
Using the RETURN Statement
Declaring Nested PL/SQL Subprograms
Passing Parameters to PL/SQL Subprograms
Actual Versus Formal Subprogram Parameters
Using Positional, Named, or Mixed Notation for Subprogram Parameters
Specifying Subprogram Parameter Modes
Using the IN Mode
Using the OUT Mode
Using the IN OUT Mode
Summary of Subprogram Parameter Modes
Using Default Values for Subprogram Parameters
Overloading Subprogram Names
Guidelines for Overloading with Numeric Types
Restrictions on Overloading
How Subprogram Calls Are Resolved
How Overloading Works with Inheritance
Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)
Advantages of Invoker's Rights
Specifying the Privileges for a Subprogram with the AUTHID Clause
Who Is the Current User During Subprogram Execution?
How External References Are Resolved in Invoker's Rights Subprograms
The Need for Template Objects in Invoker's Rights Subprograms
Overriding Default Name Resolution in Invoker's Rights Subprograms
Granting Privileges on Invoker's Rights Subprograms
Granting Privileges on an Invoker's Rights Subprogram: Example
Using Roles with Invoker's Rights Subprograms
Using Views and Database Triggers with Invoker's Rights Subprograms
Using Database Links with Invoker's Rights Subprograms
Using Object Types with Invoker's Rights Subprograms
Calling Invoker's Rights Instance Methods
Using Recursion with PL/SQL
What Is a Recursive Subprogram?
Calling External Subprograms
Controlling Side Effects of PL/SQL Subprograms
Understanding Subprogram Parameter Aliasing

9 Using PL/SQL Packages

What Is a PL/SQL Package?
What Goes In a PL/SQL Package?
Advantages of PL/SQL Packages
Understanding The Package Specification
Referencing Package Contents
Restrictions
Understanding The Package Body
Some Examples of Package Features
Private Versus Public Items in Packages
How Package STANDARD Defines the PL/SQL Environment
Overview of Product-Specific Packages
About the DBMS_ALERT Package
About the DBMS_OUTPUT Package
About the DBMS_PIPE Package
About the HTF and HTP Packages
About the UTL_FILE Package
About the UTL_HTTP Package
About the UTL_SMTP Package
Guidelines for Writing Packages
Separating Cursor Specs and Bodies with Packages

10 Handling PL/SQL Errors

Overview of PL/SQL Runtime Error Handling
Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
Advantages of PL/SQL Exceptions
Summary of Predefined PL/SQL Exceptions
Defining Your Own PL/SQL Exceptions
Declaring PL/SQL Exceptions
Scope Rules for PL/SQL Exceptions
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR
Redeclaring Predefined Exceptions
How PL/SQL Exceptions Are Raised
Raising Exceptions with the RAISE Statement
How PL/SQL Exceptions Propagate
Reraising a PL/SQL Exception
Handling Raised PL/SQL Exceptions
Exceptions Raised in Declarations
Handling Exceptions Raised in Handlers
Branching to or from an Exception Handler
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
Catching Unhandled Exceptions
Tips for Handling PL/SQL Errors
Continuing after an Exception Is Raised
Retrying a Transaction
Using Locator Variables to Identify Exception Locations
Overview of PL/SQL Compile-Time Warnings
PL/SQL Warning Categories
Controlling PL/SQL Warning Messages
Using the DBMS_WARNING Package

11 Tuning PL/SQL Applications for Performance

Initialization Parameters for PL/SQL Compilation
How PL/SQL Optimizes Your Programs
When to Tune PL/SQL Code
Guidelines for Avoiding PL/SQL Performance Problems
Avoiding CPU Overhead in PL/SQL Code
Make SQL Statements as Efficient as Possible
Make Function Calls as Efficient as Possible
Make Loops as Efficient as Possible
Do Not Duplicate Built-in String Functions
Reorder Conditional Tests to Put the Least Expensive First
Minimize Datatype Conversions
Use PLS_INTEGER for Integer Arithmetic
Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic
Avoiding Memory Overhead in PL/SQL Code
Be Generous When Declaring Sizes for VARCHAR2 Variables
Group Related Subprograms into Packages
Pin Packages in the Shared Memory Pool
Improve Your Code to Avoid Compiler Warnings
Profiling and Tracing PL/SQL Programs
Using The Profiler API: Package DBMS_PROFILER
Using The Trace API: Package DBMS_TRACE
Controlling the Trace
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
Using the FORALL Statement
How FORALL Affects Rollbacks
Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute
Retrieving Query Results into Collections with the BULK COLLECT Clause
Examples of Bulk-Fetching from a Cursor
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
Retrieving DML Results into a Collection with the RETURNING INTO Clause
Using FORALL and BULK COLLECT Together
Using Host Arrays with Bulk Binds
Writing Computation-Intensive Programs in PL/SQL
Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
Restrictions on NOCOPY
Compiling PL/SQL Code for Native Execution
Before You Begin
Determining Whether to Use PL/SQL Native Compilation
How PL/SQL Native Compilation Works
Dependencies, Invalidation and Revalidation
Real Application Clusters and PL/SQL Native Compilation
Limitations of Native Compilation
The spnc_commands File
Setting up Initialization Parameters for PL/SQL Native Compilation
PLSQL_NATIVE_LIBRARY_DIR Initialization Parameter
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT Initialization Parameter
PLSQL_CODE_TYPE Initialization Parameter
Setting Up PL/SQL Native Library Subdirectories
Setting Up and Testing PL/SQL Native Compilation
Setting Up a New Database for PL/SQL Native Compilation
Modifying the Entire Database for PL/SQL Native or Interpreted Compilation
Setting Up Transformations with Pipelined Functions
Overview of Pipelined Table Functions
Writing a Pipelined Table Function
Using Pipelined Table Functions for Transformations
Returning Results from Pipelined Table Functions
Pipelining Data Between PL/SQL Table Functions
Optimizing Multiple Calls to Pipelined Table Functions
Fetching from the Results of Pipelined Table Functions
Passing Data with Cursor Variables
Performing DML Operations Inside Pipelined Table Functions
Performing DML Operations on Pipelined Table Functions
Handling Exceptions in Pipelined Table Functions

12 Using PL/SQL With Object Types

Declaring and Initializing Objects in PL/SQL
Declaring Objects in a PL/SQL Block
How PL/SQL Treats Uninitialized Objects
Manipulating Objects in PL/SQL
Accessing Object Attributes With Dot Notation
Calling Object Constructors and Methods
Updating and Deleting Objects
Manipulating Objects Through Ref Modifiers
Defining SQL Types Equivalent to PL/SQL Collection Types
Manipulating Individual Collection Elements with SQL
Using PL/SQL Collections with SQL Object Types
Using Dynamic SQL With Objects

13 PL/SQL Language Elements

Assignment Statement
AUTONOMOUS_TRANSACTION Pragma
Block Declaration
CASE Statement
CLOSE Statement
Collection Definition
Collection Methods
Comments
COMMIT Statement
Constant and Variable Declaration
Cursor Attributes
Cursor Variables
Cursor Declaration
DELETE Statement
EXCEPTION_INIT Pragma
Exception Definition
EXECUTE IMMEDIATE Statement
EXIT Statement
Expression Definition
FETCH Statement
FORALL Statement
Function Declaration
GOTO Statement
IF Statement
INSERT Statement
Literal Declaration
LOCK TABLE Statement
LOOP Statements
MERGE Statement
NULL Statement
Object Type Declaration
OPEN Statement
OPEN-FOR Statement
Package Declaration
Procedure Declaration
RAISE Statement
Record Definition
RESTRICT_REFERENCES Pragma
RETURN Statement
RETURNING INTO Clause
ROLLBACK Statement
%ROWTYPE Attribute
SAVEPOINT Statement
SELECT INTO Statement
SERIALLY_REUSABLE Pragma
SET TRANSACTION Statement
SQL Cursor
SQLCODE Function
SQLERRM Function
%TYPE Attribute
UPDATE Statement

A Obfuscating PL/SQL Source Code

What is Obfuscation?
Tips When Obfuscating PL/SQL Units
Limitations of Obfuscation
Limitations of the wrap Utility
Limitations of the DBMS_DDL wrap Function
Obfuscating PL/SQL Code With the wrap Utility
Input and Output Files for the PL/SQL wrap Utility
Running the wrap Utility
Obfuscating PL/QL Code With DBMS_DDL Subprograms
Using the DBMS_DDL create_wrapped Procedure

B How PL/SQL Resolves Identifier Names

What Is Name Resolution?
Examples of Qualified Names and Dot Notation
Additional Examples of How to Specify Names With the Dot Notation
Differences in Name Resolution Between PL/SQL and SQL
Understanding Capture
Inner Capture
Same-Scope Capture
Outer Capture
Avoiding Inner Capture in DML Statements
Qualifying References to Object Attributes and Methods
References to Attributes and Methods
References to Row Expressions

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

Index