Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions
• Syntax Descriptions

What's New in PL/SQL?

• New PL/SQL Features for 11g Release 1 (11.1)

1 Overview of PL/SQL

• Advantages of PL/SQL
  • Tight Integration with SQL
  • Better Performance
  • Higher Productivity
  • Full Portability
  • Tight Security
  • Access to Predefined 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 Variables
    • 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
  • PL/SQL Architecture in Oracle Database Server
    • Anonymous Blocks
    • Stored Subprograms
    • Database Triggers
  • PL/SQL Architecture in Oracle Tools

2 PL/SQL Language Fundamentals

• Character Sets and Lexical Units
  • Delimiters
  • Identifiers
    • Reserved Words
    • Predefined Identifiers
    • Quoted Identifiers
  • Literals
    • Numeric Literals
    • Character Literals
    • String Literals
    • BOOLEAN Literals
    • Date and Time Literals
  • Comments
    • Single-Line Comments
    • Multiline 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
• Naming Conventions
  • Synonyms
  • Scoping
  • Case Sensitivity
  • Name Resolution
• Scope and Visibility of PL/SQL Identifiers
• Assigning Values to Variables
  • Assigning BOOLEAN Values
  • Assigning SQL Query Results to PL/SQL Variables
• PL/SQL Expressions and Comparisons
  • Operator Precedence
  • 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 BOOLEAN Expressions
  • CASE Expressions
    • Simple CASE Expression
    • Searched CASE Expression
  • Handling Null Values in Comparisons and Conditional Statements
    • NULL Values and the NOT Operator
    • NULL Values and Zero-Length Strings
    • NULL Values and the Concatenation Operator
    • NULL Values as Arguments to Built-In Functions
• PL/SQL Error-Reporting Functions
• Using SQL Functions in PL/SQL
• 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

3 PL/SQL Datatypes

• Predefined PL/SQL Scalar Datatypes and Subtypes
  • Predefined PL/SQL Numeric Datatypes and Subtypes
    • PLS_INTEGER and BINARY_INTEGER Datatypes
    • SIMPLE_INTEGER Subtype of PLS_INTEGER
    • BINARY_FLOAT and BINARY_DOUBLE Datatypes
    • NUMBER Datatype
  • Predefined PL/SQL Character Datatypes and Subtypes
    • CHAR and VARCHAR2 Datatypes
    • RAW Datatype
    • NCHAR and NVARCHAR2 Datatypes
    • LONG and LONG RAW Datatypes
    • ROWID and UROWID Datatypes
  • Predefined PL/SQL BOOLEAN Datatype
  • Predefined PL/SQL Datetime and Interval Datatypes
    • 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
• Predefined PL/SQL Large Object (LOB) Datatypes
  • BFILE Datatype
  • BLOB Datatype
  • CLOB Datatype
  • NCLOB Datatype
• User-Defined PL/SQL Subtypes
  • Defining Subtypes
  • Using Subtypes
    • Type Compatibility with Subtypes
    • Constraints and Default Values with Subtypes
• PL/SQL Datatype Conversion
  • Explicit Conversion
  • Implicit Conversion

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 the Simple CASE Statement
  • Using the Searched CASE Statement
  • Guidelines for IF and CASE Statements
• Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements)
  • Using the Basic LOOP Statement
  • Using the EXIT Statement
  • Using the EXIT-WHEN Statement
  • Using the CONTINUE Statement
  • Using the CONTINUE-WHEN Statement
  • Labeling a PL/SQL Loop
  • Using the WHILE-LOOP Statement
  • Using the FOR-LOOP Statement
    • How PL/SQL Loops Repeat
    • 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
  • GOTO Statement Restrictions
  • Using the NULL Statement

5 Using PL/SQL Collections and Records

• What Are PL/SQL Collections and Records?
  • Understanding PL/SQL Collections
    • Understanding PL/SQL Nested Tables
    • Understanding PL/SQL Varrays
    • Understanding PL/SQL 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
• Declaring 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 Subprogram Parameters and Function Return Values
• Assigning Values to Records
  • Comparing Records
  • Inserting Records Into the Database
  • Updating the Database with Record Values
  • Restrictions on Record Inserts and Updates
  • Querying Data Into Collections of Records

6 Using Static SQL

• Description of Static SQL
  • Data Manipulation Language (DML) Statements
  • Transaction Control Language (TCL) Statements
  • SQL Functions
  • SQL Pseudocolumns
    • CURRVAL and NEXTVAL
    • LEVEL
    • ROWID
    • ROWNUM
  • SQL Operators
    • Comparison Operators
    • Set Operators
    • Row 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)
  • Cursor FOR LOOP
    • Implicit Cursor FOR LOOP
    • Explicit Cursor FOR LOOP
  • Defining Aliases for Expression Values in a Cursor FOR Loop
• Using Subqueries
  • Using Correlated Subqueries
  • Writing Maintainable PL/SQL Subqueries
• 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 Statements)
    • 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: 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 Database Does Implicit Rollbacks
  • Ending Transactions
  • Setting Transaction Properties (SET TRANSACTION Statement)
  • Overriding Default Locking
    • Using FOR UPDATE
    • Using LOCK TABLE
    • Fetching Across Commits
• 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
    • Entering and Exiting
    • Committing and Rolling Back
    • Using Savepoints
    • Avoiding Errors with Autonomous Transactions
  • Using Autonomous Triggers
  • Invoking Autonomous Functions from SQL

7 Using Dynamic SQL

• When You Need Dynamic SQL
• Using Native Dynamic SQL
  • Using the EXECUTE IMMEDIATE Statement
  • Using the OPEN-FOR, FETCH, and CLOSE Statements
  • Repeating Placeholder Names in Dynamic SQL Statements
    • Dynamic SQL Statement is Not Anonymous Block or CALL Statement
    • Dynamic SQL Statement is Anonymous Block or CALL Statement
• Using DBMS_SQL Package
  • DBMS_SQL.TO_REFCURSOR Function
  • DBMS_SQL.TO_CURSOR_NUMBER Function
• Avoiding SQL Injection in PL/SQL
  • Overview of SQL Injection Techniques
    • Statement Modification
    • Statement Injection
  • Guarding Against SQL Injection
    • Using Bind Arguments to Guard Against SQL Injection
    • Using Validation Checks to Guard Against SQL Injection

8 Using PL/SQL Subprograms

• What Are PL/SQL Subprograms?
  • Subprogram Parts
  • Subprogram Types
  • Subprogram Calls
  • Subprogram Examples
• Why Use PL/SQL Subprograms?
• Using the RETURN Statement
• Declaring Nested PL/SQL Subprograms
• Passing Parameters to PL/SQL Subprograms
  • Formal and Actual Subprogram Parameters
  • Using Positional, Named, or Mixed Notation for PL/SQL Subprogram Parameters
  • Specifying Subprogram Parameter Modes
    • Using IN Mode
    • Using OUT Mode
    • Using IN OUT Mode
    • Summary of Subprogram Parameter Modes
  • Using Default Values for Subprogram Parameters
• Overloading PL/SQL Subprogram Names
  • Guidelines for Overloading with Numeric Types
  • Restrictions on Overloading
  • When Compiler Catches Overloading Errors
• How PL/SQL Subprogram Calls Are Resolved
• Using Invoker's Rights or Definer's Rights (AUTHID Clause)
  • Advantages of Invoker's Rights
  • Specifying Subprogram Privileges (AUTHID Clause)
  • Who Is the Current User During Subprogram Execution?
  • How External References Are Resolved in IR Subprograms
  • Need for Template Objects in IR Subprograms
  • Overriding Default Name Resolution in IR Subprograms
  • Granting Privileges to IR Subprograms
  • Using Views and Database Triggers with IR Subprograms
  • Using Database Links with IR Subprograms
  • Using Object Types with IR Subprograms
  • Invoking IR Instance Methods
• Using Recursive PL/SQL Subprograms
• Invoking External Subprograms
• Controlling Side Effects of PL/SQL Subprograms
• Understanding PL/SQL Subprogram Parameter Aliasing
• Using the Cross-Session PL/SQL Function Result Cache
  • Enabling Result-Caching for a Function
  • Developing Applications with Result-Cached Functions
  • Restrictions on Result-Cached Functions
  • Examples of Result-Cached Functions
    • Result-Cached Application Configuration Parameters
    • Result-Cached Recursive Function
  • Advanced Result-Cached Function Topics
    • Rules for a Cache Hit
    • Bypassing the Result Cache
    • Making Result-Cached Functions Handle Session-Specific Settings
    • Making Result-Cached Functions Handle Session-Specific Application Contexts
    • Choosing Result-Caching Granularity
    • Result Caches in Oracle RAC Environment
    • Managing the Result Cache
    • Hot-Patching PL/SQL Program Units on Which Result-Cached Functions Depend

9 Using Triggers

• Overview of Triggers
• Guidelines for Designing Triggers
• Privileges Required to Use Triggers
• Creating Triggers
  • Naming Triggers
  • When Does the Trigger Fire?
    • Do Import and SQL*Loader Fire Triggers?
    • How Column Lists Affect UPDATE Triggers
  • Controlling When a Trigger Fires (BEFORE and AFTER Options)
  • Ordering of Triggers
  • Modifying Complex Views (INSTEAD OF Triggers)
    • Views that Require INSTEAD OF Triggers
    • Triggers on Nested Table View Columns
    • Example: INSTEAD OF Trigger
  • Firing Triggers One or Many Times (FOR EACH ROW Option)
  • Firing Triggers Based on Conditions (WHEN Clause)
  • Compound Triggers
    • Why Use Compound Triggers?
    • Compound Trigger Sections
    • Triggering Statements of Compound Triggers
    • Compound Trigger Restrictions
    • Compound Trigger Example
    • Using Compound Triggers to Avoid Mutating-Table Error
• Coding the Trigger Body
  • Accessing Column Values in Row Triggers
    • Example: Modifying LOB Columns with a Trigger
    • INSTEAD OF Triggers on Nested Table View Columns
    • Avoiding Trigger Name Conflicts (REFERENCING Option)
    • Detecting the DML Operation that Fired a Trigger
    • Error Conditions and Exceptions in the Trigger Body
  • Triggers on Object Tables
  • Triggers and Handling Remote Exceptions
  • Restrictions on Creating Triggers
    • Maximum Trigger Size
    • SQL Statements Allowed in Trigger Bodies
    • Trigger Restrictions on LONG and LONG RAW Datatypes
    • Trigger Restrictions on Mutating Tables
    • Restrictions on Mutating Tables Relaxed
    • System Trigger Restrictions
    • Foreign Function Callouts
  • Who Uses the Trigger?
• Compiling Triggers
  • Dependencies for Triggers
  • Recompiling Triggers
• Modifying Triggers
• Debugging Triggers
• Enabling Triggers
• Disabling Triggers
• Viewing Information About Triggers
• Examples of Trigger Applications
  • Auditing with Triggers
  • Contraints and Triggers
  • Referential Integrity Using Triggers
    • Foreign Key Trigger for Child Table
    • UPDATE and DELETE RESTRICT Trigger for Parent Table
    • UPDATE and DELETE SET NULL Triggers for Parent Table
    • DELETE Cascade Trigger for Parent Table
    • UPDATE Cascade Trigger for Parent Table
    • Trigger for Complex Check Constraints
    • Complex Security Authorizations and Triggers
    • Transparent Event Logging and Triggers
    • Derived Column Values and Triggers
    • Building Complex Updatable Views Using Triggers
    • Fine-Grained Access Control Using Triggers
• Responding to Database Events Through Triggers
  • How Events Are Published Through Triggers
  • Publication Context
  • Error Handling
  • Execution Model
  • Event Attribute Functions
  • Database Events
  • Client Events

10 Using PL/SQL Packages

• What is a PL/SQL Package?
• What Goes in a PL/SQL Package?
• Advantages of PL/SQL Packages
• Understanding the PL/SQL Package Specification
• Referencing PL/SQL Package Contents
• Understanding the PL/SQL Package Body
• Examples of PL/SQL Package Features
• Private and Public Items in PL/SQL Packages
• How STANDARD Package Defines the PL/SQL Environment
• Overview of Product-Specific PL/SQL Packages
  • DBMS_ALERT Package
  • DBMS_OUTPUT Package
  • DBMS_PIPE Package
  • DBMS_CONNECTION_POOL Package
  • HTF and HTP Packages
  • UTL_FILE Package
  • UTL_HTTP Package
  • UTL_SMTP Package
• Guidelines for Writing PL/SQL Packages
• Separating Cursor Specifications and Bodies with PL/SQL Packages

11 Handling PL/SQL Errors

• Overview of PL/SQL Run-Time 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 (RAISE_APPLICATION_ERROR Procedure)
  • Redeclaring Predefined Exceptions
• How PL/SQL Exceptions Are Raised
• How PL/SQL Exceptions Propagate
• Reraising a PL/SQL Exception
• Handling Raised PL/SQL Exceptions
  • Exceptions Raised in Declarations
  • Handling Exceptions Raised in Exception Handlers
  • Branching To or from an Exception Handler
  • Retrieving the Error Code and Error Message (SQLCODE and SQLERRM Functions)
  • Catching Unhandled Exceptions
  • Guidelines for Handling PL/SQL Errors
    • Continuing Execution 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 DBMS_WARNING Package

12 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
    • Use Built-In String Functions
    • Put Least Expensive Conditional Tests First
    • Minimize Datatype Conversions
    • Use PLS_INTEGER or SIMPLE_INTEGER for Integer Arithmetic
    • Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic
  • Avoiding Memory Overhead in PL/SQL Code
    • Declare VARCHAR2 Variables of 4000 or More Characters
    • Group Related Subprograms into Packages
    • Pin Packages in the Shared Memory Pool
    • Apply Advice of Compiler Warnings
• Collecting Data About User-Defined Identifiers
• Profiling and Tracing PL/SQL Programs
  • Using the Profiler API: Package DBMS_PROFILER
  • Using the Trace API: Package DBMS_TRACE
• Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
  • Running One DML Statement Multiple Times (FORALL Statement)
    • How FORALL Affects Rollbacks
    • Counting Rows Affected by FORALL (%BULK_ROWCOUNT Attribute)
    • Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute)
  • Retrieving Query Results into Collections (BULK COLLECT Clause)
    • Examples of Bulk Fetching from a Cursor
    • Limiting Rows for a Bulk FETCH Operation (LIMIT Clause)
    • Retrieving DML Results Into a Collection (RETURNING INTO Clause)
    • Using FORALL and BULK COLLECT Together
    • Using Host Arrays with Bulk Binds
• Writing Computation-Intensive PL/SQL Programs
• Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables
• Tuning PL/SQL Subprogram Calls with NOCOPY Hint
• Compiling PL/SQL Program Units for Native Execution
  • Determining Whether to Use PL/SQL Native Compilation
  • How PL/SQL Native Compilation Works
  • Dependencies, Invalidation, and Revalidation
  • Setting PLSQL_CODE_TYPE Initialization Parameter
  • Setting Up a New Database for PL/SQL Native Compilation
  • Compiling the Entire Database for PL/SQL Native or Interpreted Compilation
• Performing Multiple Transformations with Pipelined Table 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 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

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
• CONTINUE Statement
• 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 and Definition
• GOTO Statement
• IF Statement
• INLINE Pragma
• 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 and Definition
• 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 Wrapping PL/SQL Source Code

• What is Wrapping?
  • Guidelines for Wrapping PL/SQL Units
  • Limitations of Wrapping
    • Limitations of the PL/SQL wrap Utility
    • Limitations of the DBMS_DDL wrap Function
• Wrapping PL/SQL Code with PL/SQL wrap Utility
  • Input and Output Files for the PL/SQL wrap Utility
  • Running the wrap Utility
• Wrapping PL/QL Code with DBMS_DDL Subprograms
• Using DBMS_DDL create_wrapped Procedure

B How PL/SQL Resolves Identifier Names

• What is Name Resolution?
• Examples of Qualified Names and Dot Notation
• How Name Resolution Differs in PL/SQL and SQL
• What is Capture?
  • Inner Capture
  • Same-Scope Capture
  • Outer Capture
• Avoiding Inner Capture in DML Statements
  • Qualifying References to Attributes and Methods
  • Qualifying References to Row Expressions

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

Index