List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
- Audience
- Documentation Accessibility
- Related Documents
- Conventions
- Syntax Descriptions
1 Changes in This Release for Oracle Database PL/SQL Language Reference
- New Features in Release 23ai for Oracle Database PL/SQL Language Reference
- Deprecated Features
- Desupported Features
2 Overview of PL/SQL
- Advantages of PL/SQL
- Main Features of PL/SQL
- Architecture of PL/SQL
  - PL/SQL Engine
  - PL/SQL Units and Compilation Parameters
- Protecting Sensitive Information in PL/SQL
3 PL/SQL Language Fundamentals
- Character Sets
- Lexical Units
- Declarations
- References to Identifiers
- Scope and Visibility of Identifiers
- Assigning Values to Variables
- Expressions
- Error-Reporting Functions
- Conditional Compilation
4 PL/SQL Data Types
- SQL Data Types
- PLS_INTEGER and BINARY_INTEGER Data Types
- User-Defined PL/SQL Subtypes
5 PL/SQL Control Statements
- Conditional Selection Statements
- LOOP Statements
- Sequential Control Statements
  - GOTO Statement
  - NULL Statement
6 PL/SQL Collections and Records
- Collection Types
- Associative Arrays
- Varrays (Variable-Size Arrays)
  - Appropriate Uses for Varrays
- Nested Tables
  - Important Differences Between Nested Tables and Arrays
  - Appropriate Uses for Nested Tables
- Collection Constructors
- Qualified Expressions Overview
- Assigning Values to Collection Variables
- Multidimensional Collections
- Collection Comparisons
- Collection Methods
- Collection Types Defined in Package Specifications
- Record Variables
- Assigning Values to Record Variables
- Record Comparisons
- Inserting Records into Tables
- Updating Rows with Records
- Restrictions on Record Inserts and Updates
7 PL/SQL Static SQL
- Description of Static SQL
  - Statements
  - Pseudocolumns
    - CURRVAL and NEXTVAL in PL/SQL
- Cursors Overview
  - Implicit Cursors
  - Explicit Cursors
- Processing Query Result Sets
- Cursor Variables
- CURSOR Expressions
- Transaction Processing and Control
- Autonomous Transactions
8 PL/SQL Dynamic SQL
- When You Need Dynamic SQL
- Native Dynamic SQL
- DBMS_SQL Package
- SQL Injection
  - SQL Injection Techniques
  - Guards Against SQL Injection
9 PL/SQL Subprograms
- Reasons to Use Subprograms
- Nested, Package, and Standalone Subprograms
- Subprogram Invocations
- Subprogram Properties
- Subprogram Parts
  - Additional Parts for Functions
  - RETURN Statement
- Forward Declaration
- Subprogram Parameters
- Subprogram Invocation Resolution
- Overloaded Subprograms
- Recursive Subprograms
- Subprogram Side Effects
- PL/SQL Function Result Cache
- PL/SQL Functions that SQL Statements Can Invoke
- Invoker's Rights and Definer's Rights (AUTHID Property)
- External Subprograms
10 PL/SQL Triggers
- Overview of Triggers
- Reasons to Use Triggers
- DML Triggers
- Correlation Names and Pseudorecords
  - OBJECT_VALUE Pseudocolumn
- System Triggers
- Subprograms Invoked by Triggers
- Trigger Compilation, Invalidation, and Recompilation
- Exception Handling in Triggers
- Trigger Design Guidelines
- Trigger Restrictions
- Order in Which Triggers Fire
- Trigger Enabling and Disabling
- Trigger Changing and Debugging
- Triggers and Oracle Database Data Transfer Utilities
- Triggers for Publishing Events
- Views for Information About Triggers
11 PL/SQL Packages
- What is a Package?
- Reasons to Use Packages
- Package Specification
  - Appropriate Public Items
  - Creating Package Specifications
- Package Body
- Package Instantiation and Initialization
- Package State
- SERIALLY_REUSABLE Packages
- Package Writing Guidelines
- Package Example
- How STANDARD Package Defines the PL/SQL Environment
12 PL/SQL Error Handling
- Compile-Time Warnings
  - DBMS_WARNING Package
- Overview of Exception Handling
- Internally Defined Exceptions
- Predefined Exceptions
- User-Defined Exceptions
- Redeclared Predefined Exceptions
- Raising Exceptions Explicitly
  - RAISE Statement
  - RAISE_APPLICATION_ERROR Procedure
- Exception Propagation
  - Propagation of Exceptions Raised in Declarations
  - Propagation of Exceptions Raised in Exception Handlers
- Unhandled Exceptions
- Retrieving Error Code and Error Message
- Continuing Execution After Handling Exceptions
- Retrying Transactions After Handling Exceptions
- Handling Errors in Distributed Queries
13 PL/SQL Optimization and Tuning
- PL/SQL Optimizer
  - Subprogram Inlining
- Candidates for Tuning
- Minimizing CPU Overhead
- Bulk SQL and Bulk Binding
- Chaining Pipelined Table Functions for Multiple Transformations
- Overview of Polymorphic Table Functions
- Updating Large Tables in Parallel
- Collecting Data About User-Defined Identifiers
- Profiling and Tracing PL/SQL Programs
- Compiling PL/SQL Units for Native Execution
14 PL/SQL Language Elements
- ACCESSIBLE BY Clause
- AGGREGATE Clause
- Assignment Statement
- AUTONOMOUS_TRANSACTION Pragma
- Basic LOOP Statement
- Block
- Call Specification
- CASE Statement
- CLOSE Statement
- Collection Method Invocation
- Collection Variable Declaration
- Comment
- COMPILE Clause
- Constant Declaration
- CONTINUE Statement
- COVERAGE Pragma
- Cursor FOR LOOP Statement
- Cursor Variable Declaration
- Datatype Attribute
- DEFAULT COLLATION Clause
- DELETE Statement Extension
- DEPRECATE Pragma
- DETERMINISTIC Clause
- Element Specification
- EXCEPTION_INIT Pragma
- Exception Declaration
- Exception Handler
- EXECUTE IMMEDIATE Statement
- EXIT Statement
- Explicit Cursor Declaration and Definition
- Expression
- FETCH Statement
- FOR LOOP Statement
- FORALL Statement
- Formal Parameter Declaration
- Function Declaration and Definition
- GOTO Statement
- IF Statement
- Implicit Cursor Attribute
- INLINE Pragma
- Invoker’s Rights and Definer’s Rights Clause
- INSERT Statement Extension
- Iterator
- Named Cursor Attribute
- NULL Statement
- OPEN Statement
- OPEN FOR Statement
- PARALLEL_ENABLE Clause
- PIPE ROW Statement
- PIPELINED Clause
- Procedure Declaration and Definition
- Qualified Expression
- RAISE Statement
- Record Variable Declaration
- RESTRICT_REFERENCES Pragma
- RETURN Statement
- RETURNING INTO Clause
- RESULT_CACHE Clause
- %ROWTYPE Attribute
- Scalar Variable Declaration
- SELECT INTO Statement
- SERIALLY_REUSABLE Pragma
- SHARD_ENABLE Clause
- SHARING Clause
- SQL_MACRO Clause
- SQLCODE Function
- SQLERRM Function
- SUPPRESSES_WARNING_6009 Pragma
- %TYPE Attribute
- UDF Pragma
- UPDATE Statement Extensions
- WHILE LOOP Statement
15 SQL Statements for Stored PL/SQL Units
- ALTER FUNCTION Statement
- ALTER LIBRARY Statement
- ALTER PACKAGE Statement
- ALTER PROCEDURE Statement
- ALTER TRIGGER Statement
- ALTER TYPE Statement
- CREATE FUNCTION Statement
- CREATE LIBRARY Statement
- CREATE PACKAGE Statement
- CREATE PACKAGE BODY Statement
- CREATE PROCEDURE Statement
- CREATE TRIGGER Statement
- CREATE TYPE Statement
- CREATE TYPE BODY Statement
- DROP FUNCTION Statement
- DROP LIBRARY Statement
- DROP PACKAGE Statement
- DROP PROCEDURE Statement
- DROP TRIGGER Statement
- DROP TYPE Statement
- DROP TYPE BODY Statement
A PL/SQL Source Text Wrapping
- PL/SQL Source Text Wrapping Limitations
- PL/SQL Source Text Wrapping Guidelines
- Wrapping PL/SQL Source Text with PL/SQL Wrapper Utility
- Wrapping PL/SQL Source Text with DBMS_DDL Subprograms
B PL/SQL Name Resolution
- Qualified Names and Dot Notation
- Column Name Precedence
- Differences Between PL/SQL and SQL Name Resolution Rules
- Resolution of Names in Static SQL Statements
- What is Capture?
- Avoiding Inner Capture in SELECT and 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
E PL/SQL Predefined Data Types
Index