Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

Database PL/SQL Language Reference

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • List of Examples
  • List of Figures
  • List of Tables
  • Title and Copyright Information
  • Preface
    • Audience
    • Documentation Accessibility
    • Related Documents
    • Conventions
    • Syntax Descriptions
  • Changes in This Release for Oracle Database PL/SQL Language Reference
    • Changes in Oracle Database Release 19c
    • Changes in Oracle Database Release 18c
      • Non-Persistance Support for Object Types
      • Polymorphic Table Functions
      • Qualified Expressions
    • Deprecated Features
    • Desupported Features
  • 1 Overview of PL/SQL
    • 1.1 Advantages of PL/SQL
      • 1.1.1 Tight Integration with SQL
      • 1.1.2 High Performance
      • 1.1.3 High Productivity
      • 1.1.4 Portability
      • 1.1.5 Scalability
      • 1.1.6 Manageability
      • 1.1.7 Support for Object-Oriented Programming
    • 1.2 Main Features of PL/SQL
      • 1.2.1 Error Handling
      • 1.2.2 Blocks
      • 1.2.3 Variables and Constants
      • 1.2.4 Subprograms
      • 1.2.5 Packages
      • 1.2.6 Triggers
      • 1.2.7 Input and Output
      • 1.2.8 Data Abstraction
        • 1.2.8.1 Cursors
        • 1.2.8.2 Composite Variables
        • 1.2.8.3 Using the %ROWTYPE Attribute
        • 1.2.8.4 Using the %TYPE Attribute
        • 1.2.8.5 Abstract Data Types
      • 1.2.9 Control Statements
      • 1.2.10 Conditional Compilation
      • 1.2.11 Processing a Query Result Set One Row at a Time
    • 1.3 Architecture of PL/SQL
      • 1.3.1 PL/SQL Engine
      • 1.3.2 PL/SQL Units and Compilation Parameters
  • 2 PL/SQL Language Fundamentals
    • 2.1 Character Sets
      • 2.1.1 Database Character Set
      • 2.1.2 National Character Set
      • 2.1.3 About Data-Bound Collation
    • 2.2 Lexical Units
      • 2.2.1 Delimiters
      • 2.2.2 Identifiers
        • 2.2.2.1 Reserved Words and Keywords
        • 2.2.2.2 Predefined Identifiers
        • 2.2.2.3 User-Defined Identifiers
          • 2.2.2.3.1 Ordinary User-Defined Identifiers
          • 2.2.2.3.2 Quoted User-Defined Identifiers
      • 2.2.3 Literals
      • 2.2.4 Pragmas
      • 2.2.5 Comments
        • 2.2.5.1 Single-Line Comments
        • 2.2.5.2 Multiline Comments
      • 2.2.6 Whitespace Characters Between Lexical Units
    • 2.3 Declarations
      • 2.3.1 NOT NULL Constraint
      • 2.3.2 Declaring Variables
      • 2.3.3 Declaring Constants
      • 2.3.4 Initial Values of Variables and Constants
      • 2.3.5 Declaring Items using the %TYPE Attribute
    • 2.4 References to Identifiers
    • 2.5 Scope and Visibility of Identifiers
    • 2.6 Assigning Values to Variables
      • 2.6.1 Assigning Values to Variables with the Assignment Statement
      • 2.6.2 Assigning Values to Variables with the SELECT INTO Statement
      • 2.6.3 Assigning Values to Variables as Parameters of a Subprogram
      • 2.6.4 Assigning Values to BOOLEAN Variables
    • 2.7 Expressions
      • 2.7.1 Concatenation Operator
      • 2.7.2 Operator Precedence
      • 2.7.3 Logical Operators
      • 2.7.4 Short-Circuit Evaluation
      • 2.7.5 Comparison Operators
        • 2.7.5.1 IS [NOT] NULL Operator
        • 2.7.5.2 Relational Operators
          • 2.7.5.2.1 Arithmetic Comparisons
          • 2.7.5.2.2 BOOLEAN Comparisons
          • 2.7.5.2.3 Character Comparisons
          • 2.7.5.2.4 Date Comparisons
        • 2.7.5.3 LIKE Operator
        • 2.7.5.4 BETWEEN Operator
        • 2.7.5.5 IN Operator
      • 2.7.6 BOOLEAN Expressions
      • 2.7.7 CASE Expressions
        • 2.7.7.1 Simple CASE Expression
        • 2.7.7.2 Searched CASE Expression
      • 2.7.8 SQL Functions in PL/SQL Expressions
      • 2.7.9 Static Expressions
        • 2.7.9.1 PLS_INTEGER Static Expressions
        • 2.7.9.2 BOOLEAN Static Expressions
        • 2.7.9.3 VARCHAR2 Static Expressions
        • 2.7.9.4 Static Constants
    • 2.8 Error-Reporting Functions
    • 2.9 Conditional Compilation
      • 2.9.1 How Conditional Compilation Works
        • 2.9.1.1 Preprocessor Control Tokens
        • 2.9.1.2 Selection Directives
        • 2.9.1.3 Error Directives
        • 2.9.1.4 Inquiry Directives
          • 2.9.1.4.1 Predefined Inquiry Directives
          • 2.9.1.4.2 Assigning Values to Inquiry Directives
          • 2.9.1.4.3 Unresolvable Inquiry Directives
        • 2.9.1.5 DBMS_DB_VERSION Package
      • 2.9.2 Conditional Compilation Examples
      • 2.9.3 Retrieving and Printing Post-Processed Source Text
      • 2.9.4 Conditional Compilation Directive Restrictions
  • 3 PL/SQL Data Types
    • 3.1 SQL Data Types
      • 3.1.1 Different Maximum Sizes
      • 3.1.2 Additional PL/SQL Constants for BINARY_FLOAT and BINARY_DOUBLE
      • 3.1.3 Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE
      • 3.1.4 CHAR and VARCHAR2 Variables
        • 3.1.4.1 Assigning or Inserting Too-Long Values
        • 3.1.4.2 Declaring Variables for Multibyte Characters
        • 3.1.4.3 Differences Between CHAR and VARCHAR2 Data Types
          • 3.1.4.3.1 Predefined Subtypes
          • 3.1.4.3.2 How Blank-Padding Works
          • 3.1.4.3.3 Value Comparisons
      • 3.1.5 LONG and LONG RAW Variables
      • 3.1.6 ROWID and UROWID Variables
    • 3.2 BOOLEAN Data Type
    • 3.3 PLS_INTEGER and BINARY_INTEGER Data Types
      • 3.3.1 Preventing PLS_INTEGER Overflow
      • 3.3.2 Predefined PLS_INTEGER Subtypes
      • 3.3.3 SIMPLE_INTEGER Subtype of PLS_INTEGER
        • 3.3.3.1 SIMPLE_INTEGER Overflow Semantics
        • 3.3.3.2 Expressions with Both SIMPLE_INTEGER and Other Operands
        • 3.3.3.3 Integer Literals in SIMPLE_INTEGER Range
    • 3.4 User-Defined PL/SQL Subtypes
      • 3.4.1 Unconstrained Subtypes
      • 3.4.2 Constrained Subtypes
      • 3.4.3 Subtypes with Base Types in Same Data Type Family
  • 4 PL/SQL Control Statements
    • 4.1 Conditional Selection Statements
      • 4.1.1 IF THEN Statement
      • 4.1.2 IF THEN ELSE Statement
      • 4.1.3 IF THEN ELSIF Statement
      • 4.1.4 Simple CASE Statement
      • 4.1.5 Searched CASE Statement
    • 4.2 LOOP Statements
      • 4.2.1 Basic LOOP Statement
      • 4.2.2 EXIT Statement
      • 4.2.3 EXIT WHEN Statement
      • 4.2.4 CONTINUE Statement
      • 4.2.5 CONTINUE WHEN Statement
      • 4.2.6 FOR LOOP Statement
        • 4.2.6.1 FOR LOOP Index
        • 4.2.6.2 Lower Bound and Upper Bound
        • 4.2.6.3 EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement
      • 4.2.7 WHILE LOOP Statement
    • 4.3 Sequential Control Statements
      • 4.3.1 GOTO Statement
      • 4.3.2 NULL Statement
  • 5 PL/SQL Collections and Records
    • 5.1 Collection Types
    • 5.2 Associative Arrays
      • 5.2.1 Declaring Associative Array Constants
      • 5.2.2 NLS Parameter Values Affect Associative Arrays Indexed by String
        • 5.2.2.1 Changing NLS Parameter Values After Populating Associative Arrays
        • 5.2.2.2 Indexes of Data Types Other Than VARCHAR2
        • 5.2.2.3 Passing Associative Arrays to Remote Databases
      • 5.2.3 Appropriate Uses for Associative Arrays
    • 5.3 Varrays (Variable-Size Arrays)
      • 5.3.1 Appropriate Uses for Varrays
    • 5.4 Nested Tables
      • 5.4.1 Important Differences Between Nested Tables and Arrays
      • 5.4.2 Appropriate Uses for Nested Tables
    • 5.5 Collection Constructors
    • 5.6 Qualified Expressions Overview
    • 5.7 Assigning Values to Collection Variables
      • 5.7.1 Data Type Compatibility
      • 5.7.2 Assigning Null Values to Varray or Nested Table Variables
      • 5.7.3 Assigning Set Operation Results to Nested Table Variables
    • 5.8 Multidimensional Collections
    • 5.9 Collection Comparisons
      • 5.9.1 Comparing Varray and Nested Table Variables to NULL
      • 5.9.2 Comparing Nested Tables for Equality and Inequality
      • 5.9.3 Comparing Nested Tables with SQL Multiset Conditions
    • 5.10 Collection Methods
      • 5.10.1 DELETE Collection Method
      • 5.10.2 TRIM Collection Method
      • 5.10.3 EXTEND Collection Method
      • 5.10.4 EXISTS Collection Method
      • 5.10.5 FIRST and LAST Collection Methods
        • 5.10.5.1 FIRST and LAST Methods for Associative Array
        • 5.10.5.2 FIRST and LAST Methods for Varray
        • 5.10.5.3 FIRST and LAST Methods for Nested Table
      • 5.10.6 COUNT Collection Method
        • 5.10.6.1 COUNT Method for Varray
        • 5.10.6.2 COUNT Method for Nested Table
      • 5.10.7 LIMIT Collection Method
      • 5.10.8 PRIOR and NEXT Collection Methods
    • 5.11 Collection Types Defined in Package Specifications
    • 5.12 Record Variables
      • 5.12.1 Initial Values of Record Variables
      • 5.12.2 Declaring Record Constants
      • 5.12.3 RECORD Types
      • 5.12.4 Declaring Items using the %ROWTYPE Attribute
        • 5.12.4.1 Declaring a Record Variable that Always Represents Full Row
        • 5.12.4.2 Declaring a Record Variable that Can Represent Partial Row
        • 5.12.4.3 %ROWTYPE Attribute and Virtual Columns
        • 5.12.4.4 %ROWTYPE Attribute and Invisible Columns
    • 5.13 Assigning Values to Record Variables
      • 5.13.1 Assigning One Record Variable to Another
      • 5.13.2 Assigning Full or Partial Rows to Record Variables
        • 5.13.2.1 Using SELECT INTO to Assign a Row to a Record Variable
        • 5.13.2.2 Using FETCH to Assign a Row to a Record Variable
        • 5.13.2.3 Using SQL Statements to Return Rows in PL/SQL Record Variables
      • 5.13.3 Assigning NULL to a Record Variable
    • 5.14 Record Comparisons
    • 5.15 Inserting Records into Tables
    • 5.16 Updating Rows with Records
    • 5.17 Restrictions on Record Inserts and Updates
  • 6 PL/SQL Static SQL
    • 6.1 Description of Static SQL
      • 6.1.1 Statements
      • 6.1.2 Pseudocolumns
        • 6.1.2.1 CURRVAL and NEXTVAL in PL/SQL
    • 6.2 Cursors Overview
      • 6.2.1 Implicit Cursors
        • 6.2.1.1 SQL%ISOPEN Attribute: Is the Cursor Open?
        • 6.2.1.2 SQL%FOUND Attribute: Were Any Rows Affected?
        • 6.2.1.3 SQL%NOTFOUND Attribute: Were No Rows Affected?
        • 6.2.1.4 SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
      • 6.2.2 Explicit Cursors
        • 6.2.2.1 Declaring and Defining Explicit Cursors
        • 6.2.2.2 Opening and Closing Explicit Cursors
        • 6.2.2.3 Fetching Data with Explicit Cursors
        • 6.2.2.4 Variables in Explicit Cursor Queries
        • 6.2.2.5 When Explicit Cursor Queries Need Column Aliases
        • 6.2.2.6 Explicit Cursors that Accept Parameters
          • 6.2.2.6.1 Formal Cursor Parameters with Default Values
          • 6.2.2.6.2 Adding Formal Cursor Parameters with Default Values
        • 6.2.2.7 Explicit Cursor Attributes
          • 6.2.2.7.1 %ISOPEN Attribute: Is the Cursor Open?
          • 6.2.2.7.2 %FOUND Attribute: Has a Row Been Fetched?
          • 6.2.2.7.3 %NOTFOUND Attribute: Has No Row Been Fetched?
          • 6.2.2.7.4 %ROWCOUNT Attribute: How Many Rows Were Fetched?
    • 6.3 Processing Query Result Sets
      • 6.3.1 Processing Query Result Sets With SELECT INTO Statements
        • 6.3.1.1 Handling Single-Row Result Sets
        • 6.3.1.2 Handling Large Multiple-Row Result Sets
      • 6.3.2 Processing Query Result Sets With Cursor FOR LOOP Statements
      • 6.3.3 Processing Query Result Sets With Explicit Cursors, OPEN, FETCH, and CLOSE
      • 6.3.4 Processing Query Result Sets with Subqueries
    • 6.4 Cursor Variables
      • 6.4.1 Creating Cursor Variables
      • 6.4.2 Opening and Closing Cursor Variables
      • 6.4.3 Fetching Data with Cursor Variables
      • 6.4.4 Assigning Values to Cursor Variables
      • 6.4.5 Variables in Cursor Variable Queries
      • 6.4.6 Querying a Collection
      • 6.4.7 Cursor Variable Attributes
      • 6.4.8 Cursor Variables as Subprogram Parameters
      • 6.4.9 Cursor Variables as Host Variables
    • 6.5 CURSOR Expressions
    • 6.6 Transaction Processing and Control
      • 6.6.1 COMMIT Statement
      • 6.6.2 ROLLBACK Statement
      • 6.6.3 SAVEPOINT Statement
      • 6.6.4 Implicit Rollbacks
      • 6.6.5 SET TRANSACTION Statement
      • 6.6.6 Overriding Default Locking
        • 6.6.6.1 LOCK TABLE Statement
        • 6.6.6.2 SELECT FOR UPDATE and FOR UPDATE Cursors
        • 6.6.6.3 Simulating CURRENT OF Clause with ROWID Pseudocolumn
    • 6.7 Autonomous Transactions
      • 6.7.1 Advantages of Autonomous Transactions
      • 6.7.2 Transaction Context
      • 6.7.3 Transaction Visibility
      • 6.7.4 Declaring Autonomous Routines
      • 6.7.5 Controlling Autonomous Transactions
        • 6.7.5.1 Entering and Exiting Autonomous Routines
        • 6.7.5.2 Committing and Rolling Back Autonomous Transactions
        • 6.7.5.3 Savepoints
        • 6.7.5.4 Avoiding Errors with Autonomous Transactions
      • 6.7.6 Autonomous Triggers
      • 6.7.7 Invoking Autonomous Functions from SQL
  • 7 PL/SQL Dynamic SQL
    • 7.1 When You Need Dynamic SQL
    • 7.2 Native Dynamic SQL
      • 7.2.1 EXECUTE IMMEDIATE Statement
      • 7.2.2 OPEN FOR, FETCH, and CLOSE Statements
      • 7.2.3 Repeated Placeholder Names in Dynamic SQL Statements
        • 7.2.3.1 Dynamic SQL Statement is Not Anonymous Block or CALL Statement
        • 7.2.3.2 Dynamic SQL Statement is Anonymous Block or CALL Statement
    • 7.3 DBMS_SQL Package
      • 7.3.1 DBMS_SQL.RETURN_RESULT Procedure
      • 7.3.2 DBMS_SQL.GET_NEXT_RESULT Procedure
      • 7.3.3 DBMS_SQL.TO_REFCURSOR Function
      • 7.3.4 DBMS_SQL.TO_CURSOR_NUMBER Function
    • 7.4 SQL Injection
      • 7.4.1 SQL Injection Techniques
        • 7.4.1.1 Statement Modification
        • 7.4.1.2 Statement Injection
        • 7.4.1.3 Data Type Conversion
      • 7.4.2 Guards Against SQL Injection
        • 7.4.2.1 Bind Variables
        • 7.4.2.2 Validation Checks
        • 7.4.2.3 Explicit Format Models
  • 8 PL/SQL Subprograms
    • 8.1 Reasons to Use Subprograms
    • 8.2 Nested, Package, and Standalone Subprograms
    • 8.3 Subprogram Invocations
    • 8.4 Subprogram Properties
    • 8.5 Subprogram Parts
      • 8.5.1 Additional Parts for Functions
      • 8.5.2 RETURN Statement
        • 8.5.2.1 RETURN Statement in Function
        • 8.5.2.2 RETURN Statement in Procedure
        • 8.5.2.3 RETURN Statement in Anonymous Block
    • 8.6 Forward Declaration
    • 8.7 Subprogram Parameters
      • 8.7.1 Formal and Actual Subprogram Parameters
        • 8.7.1.1 Formal Parameters of Constrained Subtypes
      • 8.7.2 Subprogram Parameter Passing Methods
      • 8.7.3 Subprogram Parameter Modes
      • 8.7.4 Subprogram Parameter Aliasing
        • 8.7.4.1 Subprogram Parameter Aliasing with Parameters Passed by Reference
        • 8.7.4.2 Subprogram Parameter Aliasing with Cursor Variable Parameters
      • 8.7.5 Default Values for IN Subprogram Parameters
      • 8.7.6 Positional, Named, and Mixed Notation for Actual Parameters
    • 8.8 Subprogram Invocation Resolution
    • 8.9 Overloaded Subprograms
      • 8.9.1 Formal Parameters that Differ Only in Numeric Data Type
      • 8.9.2 Subprograms that You Cannot Overload
      • 8.9.3 Subprogram Overload Errors
    • 8.10 Recursive Subprograms
    • 8.11 Subprogram Side Effects
    • 8.12 PL/SQL Function Result Cache
      • 8.12.1 Enabling Result-Caching for a Function
      • 8.12.2 Developing Applications with Result-Cached Functions
      • 8.12.3 Requirements for Result-Cached Functions
      • 8.12.4 Examples of Result-Cached Functions
        • 8.12.4.1 Result-Cached Application Configuration Parameters
        • 8.12.4.2 Result-Cached Recursive Function
      • 8.12.5 Advanced Result-Cached Function Topics
        • 8.12.5.1 Rules for a Cache Hit
        • 8.12.5.2 Result Cache Bypass
        • 8.12.5.3 Making Result-Cached Functions Handle Session-Specific Settings
        • 8.12.5.4 Making Result-Cached Functions Handle Session-Specific Application Contexts
        • 8.12.5.5 Choosing Result-Caching Granularity
        • 8.12.5.6 Result Caches in Oracle RAC Environment
        • 8.12.5.7 Result Cache Management
        • 8.12.5.8 Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend
    • 8.13 PL/SQL Functions that SQL Statements Can Invoke
    • 8.14 Invoker's Rights and Definer's Rights (AUTHID Property)
      • 8.14.1 Granting Roles to PL/SQL Packages and Standalone Subprograms
      • 8.14.2 IR Units Need Template Objects
      • 8.14.3 Connected User Database Links in DR Units
    • 8.15 External Subprograms
  • 9 PL/SQL Triggers
    • 9.1 Overview of Triggers
    • 9.2 Reasons to Use Triggers
    • 9.3 DML Triggers
      • 9.3.1 Conditional Predicates for Detecting Triggering DML Statement
      • 9.3.2 INSTEAD OF DML Triggers
      • 9.3.3 Compound DML Triggers
        • 9.3.3.1 Compound DML Trigger Structure
        • 9.3.3.2 Compound DML Trigger Restrictions
        • 9.3.3.3 Performance Benefit of Compound DML Triggers
        • 9.3.3.4 Using Compound DML Triggers with Bulk Insertion
        • 9.3.3.5 Using Compound DML Triggers to Avoid Mutating-Table Error
      • 9.3.4 Triggers for Ensuring Referential Integrity
        • 9.3.4.1 Foreign Key Trigger for Child Table
        • 9.3.4.2 UPDATE and DELETE RESTRICT Trigger for Parent Table
        • 9.3.4.3 UPDATE and DELETE SET NULL Trigger for Parent Table
        • 9.3.4.4 DELETE CASCADE Trigger for Parent Table
        • 9.3.4.5 UPDATE CASCADE Trigger for Parent Table
        • 9.3.4.6 Triggers for Complex Constraint Checking
        • 9.3.4.7 Triggers for Complex Security Authorizations
        • 9.3.4.8 Triggers for Transparent Event Logging
        • 9.3.4.9 Triggers for Deriving Column Values
        • 9.3.4.10 Triggers for Building Complex Updatable Views
        • 9.3.4.11 Triggers for Fine-Grained Access Control
    • 9.4 Correlation Names and Pseudorecords
      • 9.4.1 OBJECT_VALUE Pseudocolumn
    • 9.5 System Triggers
      • 9.5.1 SCHEMA Triggers
      • 9.5.2 DATABASE Triggers
      • 9.5.3 INSTEAD OF CREATE Triggers
    • 9.6 Subprograms Invoked by Triggers
    • 9.7 Trigger Compilation, Invalidation, and Recompilation
    • 9.8 Exception Handling in Triggers
    • 9.9 Trigger Design Guidelines
    • 9.10 Trigger Restrictions
      • 9.10.1 Trigger Size Restriction
      • 9.10.2 Trigger LONG and LONG RAW Data Type Restrictions
      • 9.10.3 Mutating-Table Restriction
    • 9.11 Order in Which Triggers Fire
    • 9.12 Trigger Enabling and Disabling
    • 9.13 Trigger Changing and Debugging
    • 9.14 Triggers and Oracle Database Data Transfer Utilities
    • 9.15 Triggers for Publishing Events
      • 9.15.1 Event Attribute Functions
      • 9.15.2 Event Attribute Functions for Database Event Triggers
      • 9.15.3 Event Attribute Functions for Client Event Triggers
    • 9.16 Views for Information About Triggers
  • 10 PL/SQL Packages
    • 10.1 What is a Package?
    • 10.2 Reasons to Use Packages
    • 10.3 Package Specification
      • 10.3.1 Appropriate Public Items
      • 10.3.2 Creating Package Specifications
    • 10.4 Package Body
    • 10.5 Package Instantiation and Initialization
    • 10.6 Package State
    • 10.7 SERIALLY_REUSABLE Packages
      • 10.7.1 Creating SERIALLY_REUSABLE Packages
      • 10.7.2 SERIALLY_REUSABLE Package Work Unit
      • 10.7.3 Explicit Cursors in SERIALLY_REUSABLE Packages
    • 10.8 Package Writing Guidelines
    • 10.9 Package Example
    • 10.10 How STANDARD Package Defines the PL/SQL Environment
  • 11 PL/SQL Error Handling
    • 11.1 Compile-Time Warnings
      • 11.1.1 DBMS_WARNING Package
    • 11.2 Overview of Exception Handling
      • 11.2.1 Exception Categories
      • 11.2.2 Advantages of Exception Handlers
      • 11.2.3 Guidelines for Avoiding and Handling Exceptions
    • 11.3 Internally Defined Exceptions
    • 11.4 Predefined Exceptions
    • 11.5 User-Defined Exceptions
    • 11.6 Redeclared Predefined Exceptions
    • 11.7 Raising Exceptions Explicitly
      • 11.7.1 RAISE Statement
        • 11.7.1.1 Raising User-Defined Exception with RAISE Statement
        • 11.7.1.2 Raising Internally Defined Exception with RAISE Statement
        • 11.7.1.3 Reraising Current Exception with RAISE Statement
      • 11.7.2 RAISE_APPLICATION_ERROR Procedure
    • 11.8 Exception Propagation
      • 11.8.1 Propagation of Exceptions Raised in Declarations
      • 11.8.2 Propagation of Exceptions Raised in Exception Handlers
    • 11.9 Unhandled Exceptions
    • 11.10 Retrieving Error Code and Error Message
    • 11.11 Continuing Execution After Handling Exceptions
    • 11.12 Retrying Transactions After Handling Exceptions
    • 11.13 Handling Errors in Distributed Queries
  • 12 PL/SQL Optimization and Tuning
    • 12.1 PL/SQL Optimizer
      • 12.1.1 Subprogram Inlining
    • 12.2 Candidates for Tuning
    • 12.3 Minimizing CPU Overhead
      • 12.3.1 Tune SQL Statements
      • 12.3.2 Tune Function Invocations in Queries
      • 12.3.3 Tune Subprogram Invocations
      • 12.3.4 Tune Loops
      • 12.3.5 Tune Computation-Intensive PL/SQL Code
        • 12.3.5.1 Use Data Types that Use Hardware Arithmetic
        • 12.3.5.2 Avoid Constrained Subtypes in Performance-Critical Code
        • 12.3.5.3 Minimize Implicit Data Type Conversion
      • 12.3.6 Use SQL Character Functions
      • 12.3.7 Put Least Expensive Conditional Tests First
    • 12.4 Bulk SQL and Bulk Binding
      • 12.4.1 FORALL Statement
        • 12.4.1.1 Using FORALL Statements for Sparse Collections
        • 12.4.1.2 Unhandled Exceptions in FORALL Statements
        • 12.4.1.3 Handling FORALL Exceptions Immediately
        • 12.4.1.4 Handling FORALL Exceptions After FORALL Statement Completes
          • 12.4.1.4.1 Sparse Collections and SQL%BULK_EXCEPTIONS
        • 12.4.1.5 Getting Number of Rows Affected by FORALL Statement
      • 12.4.2 BULK COLLECT Clause
        • 12.4.2.1 SELECT INTO Statement with BULK COLLECT Clause
          • 12.4.2.1.1 SELECT BULK COLLECT INTO Statements and Aliasing
          • 12.4.2.1.2 Row Limits for SELECT BULK COLLECT INTO Statements
          • 12.4.2.1.3 Guidelines for Looping Through Collections
        • 12.4.2.2 FETCH Statement with BULK COLLECT Clause
          • 12.4.2.2.1 Row Limits for FETCH BULK COLLECT Statements
        • 12.4.2.3 RETURNING INTO Clause with BULK COLLECT Clause
      • 12.4.3 Using FORALL Statement and BULK COLLECT Clause Together
      • 12.4.4 Client Bulk-Binding of Host Arrays
    • 12.5 Chaining Pipelined Table Functions for Multiple Transformations
      • 12.5.1 Overview of Table Functions
      • 12.5.2 Creating Pipelined Table Functions
      • 12.5.3 Pipelined Table Functions as Transformation Functions
      • 12.5.4 Chaining Pipelined Table Functions
      • 12.5.5 Fetching from Results of Pipelined Table Functions
      • 12.5.6 Passing CURSOR Expressions to Pipelined Table Functions
      • 12.5.7 DML Statements on Pipelined Table Function Results
      • 12.5.8 NO_DATA_NEEDED Exception
    • 12.6 Overview of Polymorphic Table Functions
      • 12.6.1 Polymorphic Table Function Definition
      • 12.6.2 Polymorphic Table Function Implementation
      • 12.6.3 Polymorphic Table Function Invocation
        • 12.6.3.1 Variadic Pseudo-Operators
        • 12.6.3.2 COLUMNS Pseudo-Operator
      • 12.6.4 Polymorphic Table Function Compilation and Execution
      • 12.6.5 Polymorphic Table Function Optimization
      • 12.6.6 Skip_col Polymorphic Table Function Example
      • 12.6.7 To_doc Polymorphic Table Function Example
      • 12.6.8 Implicit_echo Polymorphic Table Function Example
    • 12.7 Updating Large Tables in Parallel
    • 12.8 Collecting Data About User-Defined Identifiers
    • 12.9 Profiling and Tracing PL/SQL Programs
    • 12.10 Compiling PL/SQL Units for Native Execution
      • 12.10.1 Determining Whether to Use PL/SQL Native Compilation
      • 12.10.2 How PL/SQL Native Compilation Works
      • 12.10.3 Dependencies, Invalidation, and Revalidation
      • 12.10.4 Setting Up a New Database for PL/SQL Native Compilation
      • 12.10.5 Compiling the Entire Database for PL/SQL Native or Interpreted Compilation
  • 13 PL/SQL Language Elements
    • 13.1 ACCESSIBLE BY Clause
    • 13.2 AGGREGATE Clause
    • 13.3 Assignment Statement
    • 13.4 AUTONOMOUS_TRANSACTION Pragma
    • 13.5 Basic LOOP Statement
    • 13.6 Block
    • 13.7 Call Specification
    • 13.8 CASE Statement
    • 13.9 CLOSE Statement
    • 13.10 Collection Method Invocation
    • 13.11 Collection Variable Declaration
    • 13.12 Comment
    • 13.13 COMPILE Clause
    • 13.14 Constant Declaration
    • 13.15 CONTINUE Statement
    • 13.16 COVERAGE Pragma
    • 13.17 Cursor FOR LOOP Statement
    • 13.18 Cursor Variable Declaration
    • 13.19 Datatype Attribute
    • 13.20 DEFAULT COLLATION Clause
    • 13.21 DELETE Statement Extension
    • 13.22 DEPRECATE Pragma
    • 13.23 DETERMINISTIC Clause
    • 13.24 Element Specification
    • 13.25 EXCEPTION_INIT Pragma
    • 13.26 Exception Declaration
    • 13.27 Exception Handler
    • 13.28 EXECUTE IMMEDIATE Statement
    • 13.29 EXIT Statement
    • 13.30 Explicit Cursor Declaration and Definition
    • 13.31 Expression
    • 13.32 FETCH Statement
    • 13.33 FOR LOOP Statement
    • 13.34 FORALL Statement
    • 13.35 Formal Parameter Declaration
    • 13.36 Function Declaration and Definition
    • 13.37 GOTO Statement
    • 13.38 IF Statement
    • 13.39 Implicit Cursor Attribute
    • 13.40 INLINE Pragma
    • 13.41 Invoker’s Rights and Definer’s Rights Clause
    • 13.42 INSERT Statement Extension
    • 13.43 Named Cursor Attribute
    • 13.44 NULL Statement
    • 13.45 OPEN Statement
    • 13.46 OPEN FOR Statement
    • 13.47 PARALLEL_ENABLE Clause
    • 13.48 PIPE ROW Statement
    • 13.49 PIPELINED Clause
    • 13.50 Procedure Declaration and Definition
    • 13.51 RAISE Statement
    • 13.52 Record Variable Declaration
    • 13.53 RESTRICT_REFERENCES Pragma
    • 13.54 RETURN Statement
    • 13.55 RETURNING INTO Clause
    • 13.56 RESULT_CACHE Clause
    • 13.57 %ROWTYPE Attribute
    • 13.58 Scalar Variable Declaration
    • 13.59 SELECT INTO Statement
    • 13.60 SERIALLY_REUSABLE Pragma
    • 13.61 SHARING Clause
    • 13.62 SQLCODE Function
    • 13.63 SQLERRM Function
    • 13.64 %TYPE Attribute
    • 13.65 UDF Pragma
    • 13.66 UPDATE Statement Extensions
    • 13.67 WHILE LOOP Statement
  • 14 SQL Statements for Stored PL/SQL Units
    • 14.1 ALTER FUNCTION Statement
    • 14.2 ALTER LIBRARY Statement
    • 14.3 ALTER PACKAGE Statement
    • 14.4 ALTER PROCEDURE Statement
    • 14.5 ALTER TRIGGER Statement
    • 14.6 ALTER TYPE Statement
    • 14.7 CREATE FUNCTION Statement
    • 14.8 CREATE LIBRARY Statement
    • 14.9 CREATE PACKAGE Statement
    • 14.10 CREATE PACKAGE BODY Statement
    • 14.11 CREATE PROCEDURE Statement
    • 14.12 CREATE TRIGGER Statement
    • 14.13 CREATE TYPE Statement
    • 14.14 CREATE TYPE BODY Statement
    • 14.15 DROP FUNCTION Statement
    • 14.16 DROP LIBRARY Statement
    • 14.17 DROP PACKAGE Statement
    • 14.18 DROP PROCEDURE Statement
    • 14.19 DROP TRIGGER Statement
    • 14.20 DROP TYPE Statement
    • 14.21 DROP TYPE BODY Statement
  • A PL/SQL Source Text Wrapping
    • A.1 PL/SQL Source Text Wrapping Limitations
    • A.2 PL/SQL Source Text Wrapping Guidelines
    • A.3 Wrapping PL/SQL Source Text with PL/SQL Wrapper Utility
    • A.4 Wrapping PL/SQL Source Text with DBMS_DDL Subprograms
  • B PL/SQL Name Resolution
    • B.1 Qualified Names and Dot Notation
    • B.2 Column Name Precedence
    • B.3 Differences Between PL/SQL and SQL Name Resolution Rules
    • B.4 Resolution of Names in Static SQL Statements
    • B.5 What is Capture?
      • B.5.1 Outer Capture
      • B.5.2 Same-Scope Capture
      • B.5.3 Inner Capture
    • B.6 Avoiding Inner Capture in SELECT and DML Statements
      • B.6.1 Qualifying References to Attributes and Methods
      • B.6.2 Qualifying References to Row Expressions
  • C PL/SQL Program Limits
  • D PL/SQL Reserved Words and Keywords
  • E PL/SQL Predefined Data Types
  • Index

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. List of Figures

List of Figures

  • 1-1        PL/SQL Engine
  • 5-1        Varray of Maximum Size 10 with 7 Elements
  • 5-2        Array and Nested Table
  • 6-1        Transaction Control Flow
  • 8-1        How PL/SQL Compiler Resolves Invocations
  • 11-1      Exception Does Not Propagate
  • 11-2      Exception Propagates from Inner Block to Outer Block
  • 11-3      PL/SQL Returns Unhandled Exception Error to Host Environment
  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights
  • Cookie Preferences
Copyright © 1996, 2019, Oracle and/or its affiliates. All rights reserved.
  • Previous
  • Next
Video