8/2/2019 11g_plsql

1/740

Oracle Database

PL/SQL Language Reference

11gRelease 2 (11.2)

E10472-06

March 2010

8/2/2019 11g_plsql

2/740

Oracle Database PL/SQL Language Reference, 11g Release 2 (11.2)

E10472-06

Copyright 1996, 2010, Oracle and/or its affiliates. All rights reserved.

Primary Author: Sheila Moore

Contributing Author: Eric Belden

Contributors: S. Agrawal, C. Barclay, D. Bronnikov, S. Castledine, T. Chang, B. Cheng, R. Dani, R. Decker,C. Iyer, S. Kotsovolos, N. Le, W. Li, S. Lin, B. Llewellyn, D. Lorentz, V. Moore, K. Muthukkaruppan, C.Racicot, K. Rich, J. Russell , C. Wetherell, M. Vemulapati, G. Viswanathan, M. Yang

This software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data

delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. Assuch, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions andlicense terms set forth in the applicable Government contract, and, to the extent applicable by the terms ofthe Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer SoftwareLicense (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is notdeveloped or intended for use in any inherently dangerous applications, including applications which maycreate a risk of personal injury. If you use this software in dangerous applications, then you shall beresponsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe useof this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use ofthis software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarksof their respective owners.

This software and documentation may provide access to or information on content, products, and services

from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim allwarranties of any kind with respect to third-party content, products, and services. Oracle Corporation andits affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use ofthird-party content, products, or services.

8/2/2019 11g_plsql

3/740

8/2/2019 11g_plsql

4/740

8/2/2019 11g_plsql

5/740

v

Contents

Preface ............................................................................................................................................................. xxix

Audience................................................................................................................................................... xxix

Documentation Accessibility................................................................................................................. xxix

Related Documents ............... .............. ................ .............. ............... .............. ............... .............. ............ xxx

Conventions .............. ............... .............. ............... .............. ................ .............. ................ ............... ........ xxx

Syntax Descriptions................................................................................................................................. xxxi

What's New in PL/SQL?....................................................................................................................... xxxiii

PL/SQL Features for 11g Release 2 (11.2).......................................................................................... xxxiii

PL/SQL Features for 11g Release 1 (11.1).......................................................................................... xxxv

1 Overview of PL/SQL

Advantages of PL/SQL ............................................................................................................................ 1-1

Tight Integration with SQL............................................................................................................... 1-1

High Performance.............................................................................................................................. 1-2

High Productivity ............... .............. ............... .............. ................ .............. ............... ............... ........1-2

Portability............................................................................................................................................ 1-3

Scalability ............................................................................................................................................ 1-3

Manageability ................ .............. ............... .............. ............... .............. ............... ............... .............. . 1-3

Support for Object-Oriented Programming ............... ............... ............... ............... ................ ....... 1-3

Support for Developing Web Applications.................................................................................... 1-3

Support for Developing Server Pages............................................................................................. 1-4

Main Features of PL/SQL........................................................................................................................ 1-4

Error Handling .............. .............. ............... .............. ............... .............. ................ .............. .............. . 1-4

Blocks .............. ................ .............. .............. ............... .............. ............... .............. ................ .............. . 1-5

Variables and Constants.................................................................................................................... 1-5

Subprograms....................................................................................................................................... 1-6Packages .............................................................................................................................................. 1-6

Triggers................................................................................................................................................ 1-6

Input and Output .............. .............. ............... ............... .............. ............... .............. ................ .......... 1-6

Data Abstraction................................................................................................................................. 1-7

Cursors ......................................................................................................................................... 1-7

Composite Variables................................................................................................................... 1-7

%ROWTYPE Attribute............................................................................................................... 1-8

%TYPE Attribute......................................................................................................................... 1-8

8/2/2019 11g_plsql

6/740

vi

Abstract Data Types .............. .............. ................ .............. ............... .............. ............... ............. 1-8

Control Statements............................................................................................................................. 1-8

Conditional Compilation ............... ............... .............. ................ .............. ............... ................. ........ 1-9

Processing a Query Result Set One Row at a Time....................................................................... 1-9

Architecture of PL/SQL ........................................................................................................................ 1-10

PL/SQL Engine .............. ............... .............. ................ .............. ............... .............. ............... .......... 1-10

PL/SQL Units and Compilation Parameters .............. ............... ............... ............... ............... .... 1-10

2 PL/SQL Language Fundamentals

Character Sets............................................................................................................................................ 2-1

Database Character Set...................................................................................................................... 2-1

National Character Set....................................................................................................................... 2-3

Lexical Units .............................................................................................................................................. 2-3

Delimiters .............. ............... ............... ............... .............. ................ .............. ............... ................ ...... 2-3

Identifiers ............................................................................................................................................ 2-4

Reserved Words and Keywords ............. ............... .............. ............... ............... .............. ......... 2-5

Predefined Identifiers................................................................................................................. 2-5

User-Defined Identifiers ............. ................ .............. ............... ............... ............... ................. ... 2-5

Literals ................................................................................................................................................. 2-8

Comments ........................................................................................................................................... 2-9

Single-Line Comments ............... .............. ............... ................ .............. ............... .............. ........ 2-9

Multiline Comments................................................................................................................ 2-10

Whitespace Characters Between Lexical Units........................................................................... 2-11

Declarations............................................................................................................................................ 2-11

Variable Declarations...................................................................................................................... 2-12

Constant Declarations .............. .............. ............... .............. ............... ............... ............... .............. 2-12

Initial Values of Variables and Constants.................................................................................... 2-13

NOT NULL Constraint................................................................................................................... 2-13%TYPE Attribute............................................................................................................................. 2-14

References to Identifiers ...................................................................................................................... 2-15

Scope and Visibility of Identifiers..................................................................................................... 2-16

Assigning Values to Variables ............................................................................................................ 2-20

Assigning Values to Variables with the Assignment Statement.............................................. 2-21

Assigning Values to Variables with the SELECT INTO Statement ............. ............... ............. 2-21

Assigning Values to Variables as Parameters of a Subprogram............. ............... ............... ... 2-22

Assigning Values to BOOLEAN Variables.................................................................................. 2-23

Expressions ............................................................................................................................................. 2-23

Concatenation Operator................................................................................................................. 2-24

Operator Precedence....................................................................................................................... 2-25Logical Operators............................................................................................................................ 2-27

Short-Circuit Evaluation ............... ............... ............... ................ .............. ............... ................. ..... 2-31

Comparison Operators................................................................................................................... 2-32

Relational Operators................................................................................................................ 2-32

IS [NOT] NULL Operator....................................................................................................... 2-34

LIKE Operator ............. ............... ............... .............. ............... .............. ................ ............... ..... 2-34

BETWEEN Operator................................................................................................................ 2-35

IN Operator............................................................................................................................... 2-36

8/2/2019 11g_plsql

7/740

vii

BOOLEAN Expressions ................................................................................................................. 2-37

CASE Expressions........................................................................................................................... 2-37

Simple CASE Expression .............. ............... ............... .............. ............... .............. ................ . 2-38

Searched CASE Expression .................................................................................................... 2-39

SQL Functions in PL/SQL Expressions....................................................................................... 2-40

Error-Reporting Functions ................................................................................................................... 2-41

Pragmas ................................................................................................................................................... 2-41Conditional Compilation..................................................................................................................... 2-41

How Conditional Compilation Works......................................................................................... 2-42

Preprocessor Control Tokens .............. ............... .............. ................ ............. ................ ......... 2-42

Selection Directives.................................................................................................................. 2-42

Error Directives ............. .............. ................ .............. ............... .............. ............... ................ ... 2-43

Inquiry Directives ............... ............... .............. ............... .............. ............... .............. .............. 2-43

Static Expressions..................................................................................................................... 2-46

Conditional Compilation Examples .............. ............... ............... ............... ............... ............... .... 2-49

Retrieving and Printing Post-Processed Source Text................................................................. 2-51

Conditional Compilation Directive Restrictions .............. ............... ............... .............. .............. 2-51

3 PL/SQL Data Types

SQL Data Types ........................................................................................................................................ 3-2

Different Maximum Sizes .............. ............... ............... ............... .............. ............... ............... .......... 3-2

Additional PL/SQL Constants for BINARY_FLOAT and BINARY_DOUBLE........................ 3-2

Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE........................... 3-3

CHAR and VARCHAR2 Variables.................................................................................................. 3-3

Assigning or Inserting Too-Long Values .............. ................. .............. ............... .............. ...... 3-3

Declaring Variables for Multibyte Characters........................................................................ 3-4

Differences Between CHAR and VARCHAR2 Data Types.................................................. 3-5

LONG and LONG RAW Variables.............. .............. .............. ............... .............. ............... ........... 3-6ROWID and UROWID Variables....................... .............. ............... .............. ............... ............... .... 3-7

BOOLEAN Data Type.............................................................................................................................. 3-7

PLS_INTEGER and BINARY_INTEGER Data Types ....................................................................... 3-8

Preventing PLS_INTEGER Overflow.............................................................................................. 3-8

Predefined PLS_INTEGER Subtypes .............. .............. ............... ................ .............. ............... ...... 3-9

SIMPLE_INTEGER Subtype of PLS_INTEGER.......................................................................... 3-10

SIMPLE_INTEGER Overflow Semantics .............. .............. ............... ............... ............... .... 3-10

Expressions with Both SIMPLE_INTEGER and Other Operands .............. ............... ....... 3-11

Integer Literals in SIMPLE_INTEGER Range...................................................................... 3-11

User-Defined PL/SQL Subtypes......................................................................................................... 3-11

Unconstrained Subtypes ............... ............... .............. ................ .............. ............... ............... ........ 3-12Constrained Subtypes..................................................................................................................... 3-12

Subtypes with Base Types in Same Data Type Family.............................................................. 3-14

4 PL/SQL Control Statements

Overview of PL/SQL Control Statements............................................................................................ 4-1

Conditional Selection Statements......................................................................................................... 4-1

IF THEN Statement............................................................................................................................ 4-2

8/2/2019 11g_plsql

8/740

viii

IF THEN ELSE Statement ............... .............. .............. ................ .............. .............. ............... ........... 4-3

IF THEN ELSIF Statement ............. ............... .............. ............... ............... .............. ............... ........... 4-5

Simple CASE Statement .............. .............. ............... .............. ................ .............. ............... .............. 4-7

Searched CASE Statement .............. .............. ................ ............... .............. ................ .............. ......... 4-7

LOOP Statements ..................................................................................................................................... 4-9

Basic LOOP Statement....................................................................................................................... 4-9

EXIT Statement .............. ............... ............... .............. ............... ............... .............. ................ .......... 4-10EXIT WHEN Statement.................................................................................................................. 4-10

CONTINUE Statement................................................................................................................... 4-11

CONTINUE WHEN Statement..................................................................................................... 4-12

FOR LOOP Statement..................................................................................................................... 4-13

FOR LOOP Index ............ ............... .............. ............... .............. .............. ................ .............. ... 4-15

Lower Bound and Upper Bound ............. .............. ............... ............... .............. ............... ..... 4-17

EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement..................... 4-18

WHILE LOOP Statement ............. .............. ................ .............. .............. ............... .............. ........... 4-20

Sequential Control Statements ........................................................................................................... 4-20

GOTO Statement .............. ............... .............. ............... .............. ................ .............. ............... ........ 4-21

NULL Statement.............................................................................................................................. 4-23

5 PL/SQL Collections and Records

Collection Types ....................................................................................................................................... 5-2

Associative Arrays.................................................................................................................................... 5-4

NLS Parameter Values Affect Associative Arrays Indexed by String........................................ 5-6

Changing NLS Parameter Values After Populating Associative Arrays............................ 5-6

Subscripts of Data Types Other Than VARCHAR2............................................................... 5-6

Passing Associative Arrays to Remote Databases.................................................................. 5-7

Appropriate Uses for Associative Arrays....................................................................................... 5-7

Varrays (Variable-Size Arrays) ............................................................................................................... 5-7Appropriate Uses for Varrays ............... .............. ............... .............. ............... ............... ............... ... 5-9

Nested Tables ............................................................................................................................................ 5-9

Important Differences Between Nested Tables and Arrays .............. ............... ............... ......... 5-12

Appropriate Uses for Nested Tables .............. ................ .............. ............... ............... ............... ... 5-12

Collection Constructors........................................................................................................................ 5-13

Assigning Collections to Collection Variables ................................................................................ 5-14

Data Type Compatibility................................................................................................................ 5-14

Assigning Null Values to Varray or Nested Table Variables .............. .............. ............... ........ 5-15

Assigning Set Operation Results to Nested Table Variables............ ............... .............. ........... 5-15

Multidimensional Collections ............................................................................................................ 5-17

Collection Comparisons....................................................................................................................... 5-18Comparing Varray and Nested Table Variables to NULL........................................................ 5-19

Comparing Nested Tables for Equality and Inequality ............. .............. ................ .............. ... 5-19

Comparing Nested Tables with SQL Multiset Conditions ............... ............... ................ ......... 5-20

Collection Methods............................................................................................................................... 5-21

DELETE Collection Method ............. .............. ............... ................ .............. ............... .............. ..... 5-22

TRIM Collection Method ............. ................ .............. ............... .............. ................ ............... ........ 5-25

EXTEND Collection Method ............... .............. ............... ............... ............... ............... ................ 5-26

EXISTS Collection Method............................................................................................................. 5-27

8/2/2019 11g_plsql

9/740

ix

FIRST and LAST Collection Methods ............. ............... ............... ............... ............... .............. ... 5-28

FIRST and LAST Methods for Associative Array .............. ............... .............. ............... ..... 5-28

FIRST and LAST Methods for Varray................................................................................... 5-30

FIRST and LAST Methods for Nested Table........................................................................ 5-31

COUNT Collection Method........................................................................................................... 5-32

COUNT Method for Varray ................ .............. ............... ............... .............. ................ ......... 5-32

COUNT Method for Nested Table .............. ............... .............. ................ ............... .............. 5-33LIMIT Collection Method ................ .............. ............... .............. ............... ............... .............. ....... 5-34

PRIOR and NEXT Collection Methods .............. ............... .............. ................ .............. ............... 5-35

Collection Types Defined in Package Specifications..................................................................... 5-37

Record Variables .................................................................................................................................... 5-38

Initial Values of Record Variables ................ .............. ............... .............. ................ ............... ...... 5-38

RECORD Types .............. ................ .............. ............... .............. ............... .............. .............. ........... 5-39

RECORD Types Defined in Package Specifications................................................................... 5-41

%ROWTYPE Attribute ............. ............... .............. ................ .............. ............... ............... ............. 5-42

Record Variable that Always Represents Full Row............................................................ 5-42

Record Variable that Can Represent Partial Row .............. ............... ............... ............... .... 5-43

Assigning Values to Record Variables .............................................................................................. 5-44Assigning One Record Variable to Another................................................................................ 5-45

Assigning Full or Partial Rows to Record Variables.................................................................. 5-47

SELECT INTO Statement for Assigning Row to Record Variable.................................... 5-47

FETCH Statement for Assigning Row to Record Variable .............. ................ ............... ... 5-47

SQL Statements that Return Rows in PL/SQL Record Variables..................................... 5-49

Assigning Sets of Rows to Collections of Records ......................................................................... 5-49

SELECT INTO Statement with BULK COLLECT Clause .............. ............... ................ ............ 5-50

FETCH Statement with BULK COLLECT Clause...................................................................... 5-51

SQL Statements that Return Sets of Rows in PL/SQL Collections of Records...................... 5-52

Record Comparisons............................................................................................................................. 5-52

Inserting Records into Tables ............................................................................................................. 5-54

Updating Rows with Records ............................................................................................................. 5-55

Restrictions on Record Inserts and Updates .................................................................................... 5-56

6 PL/SQL Static SQL

Description of Static SQL ....................................................................................................................... 6-1

Resolution of Names in Static SQL Statements ................................................................................. 6-3

SQL Pseudocolumns................................................................................................................................ 6-3

CURRVAL and NEXTVAL............................................................................................................... 6-4

LEVEL.................................................................................................................................................. 6-5

ROWID ................................................................................................................................................ 6-6ROWNUM........................................................................................................................................... 6-6

Cursors........................................................................................................................................................ 6-6

Implicit Cursors.................................................................................................................................. 6-7

SQL%ISOPEN Attribute: Is the Cursor Open?....................................................................... 6-7

SQL%FOUND Attribute: Were Any Rows Affected? .............. ................ ............... .............. 6-7

SQL%NOTFOUND Attribute: Were No Rows Affected?..................................................... 6-8

SQL%ROWCOUNT Attribute: How Many Rows Were Affected?.............. .............. ......... 6-8

Explicit Cursors ............. ............... .............. ................ ............. ................ .............. ................ ............. 6-9

8/2/2019 11g_plsql

10/740

x

Declaring Explicit Cursors...................................................................................................... 6-10

Opening Explicit Cursors ....................................................................................................... 6-11

Fetching Data with Explicit Cursors ............... ................ ............... .............. ............... .......... 6-11

Fetching Bulk Data with Explicit Cursors............................................................................ 6-13

Closing Explicit Cursors ......................................................................................................... 6-14

Explicit Cursor Attributes....................................................................................................... 6-14

When Explicit Cursors Need Column Aliases..................................................................... 6-18Explicit Cursors that Accept Parameters.............................................................................. 6-18

Query Result Set Processing ............................................................................................................... 6-20

Query Result Set Processing With SELECT INTO Statements................................................. 6-20

Single-Row Result Sets............................................................................................................ 6-21

Large Multiple-Row Result Sets ............. ............... ............... ............... ............... .............. ..... 6-21

Query Result Set Processing With Cursor FOR LOOP Statements ............. ............... ............. 6-22

Query Result Set Processing With Explicit Cursors, OPEN, FETCH, and CLOSE .............. . 6-23

Query Result Set Processing with Subqueries .............. .............. ............... .............. ................ ... 6-24

Cursor Variables .................................................................................................................................... 6-26

Cursor Variable Creation ............. ................ .............. ............... ............... ............... ............... ........ 6-26

Cursor Variables as Subprogram Parameters............................................................................. 6-28Cursor Variable Control (OPEN FOR, FETCH, and CLOSE Statements) .............. ................ 6-28

Opening Cursor Variables .............. ............... ............... .............. ................ .............. .............. 6-29

Fetching from Cursor Variables............................................................................................. 6-31

Closing Cursor Variables........................................................................................................ 6-32

INVALID_CURSOR Exception.............................................................................................. 6-32

Cursor Variables as Host Variables.............................................................................................. 6-33

Cursor Variable Restrictions.......................................................................................................... 6-34

Cursor Expressions................................................................................................................................ 6-35

Transaction Processing and Control .................................................................................................. 6-36

COMMIT Statement........................................................................................................................ 6-37

ROLLBACK Statement................................................................................................................... 6-39

SAVEPOINT Statement.................................................................................................................. 6-40

Implicit Rollbacks............................................................................................................................ 6-42

SET TRANSACTION Statement .............. .............. ............... ................ .............. ............... ........... 6-42

Overriding Default Locking ............. ............... .............. ................ .............. ............... ............... .... 6-43

LOCK TABLE Statement......................................................................................................... 6-44

SELECT FOR UPDATE and FOR UPDATE Cursors.......................................................... 6-44

Simulating CURRENT OF Clause with ROWID Pseudocolumn .............. .............. ......... 6-45

Autonomous Transactions ................................................................................................................... 6-47

Advantages of Autonomous Transactions.................................................................................. 6-48

Transaction Context........................................................................................................................ 6-48

Transaction Visibility...................................................................................................................... 6-48

Declaring Autonomous Transactions........................................................................................... 6-48

Controlling Autonomous Transactions .............. ................ .............. ............... ............... ............. 6-49

Entering and Exiting................................................................................................................ 6-50

Committing and Rolling Back................................................................................................ 6-50

Savepoints .............. .............. ................ .............. ............... .............. ............... .............. ............. 6-50

Avoiding Errors with Autonomous Transactions............................................................... 6-50

Autonomous Triggers .............. ............... .............. ............... .............. ............... ............... .............. 6-51

8/2/2019 11g_plsql

11/740

xi

Invoking Autonomous Functions from SQL............................................................................... 6-53

7 PL/SQL Dynamic SQL

When You Need Dynamic SQL ............................................................................................................. 7-1

Native Dynamic SQL ............................................................................................................................... 7-2

EXECUTE IMMEDIATE Statement................................................................................................. 7-2

OPEN FOR, FETCH, and CLOSE Statements................................................................................ 7-4

Repeated Placeholder Names in Dynamic SQL Statements........................................................ 7-5

Dynamic SQL Statement is Not Anonymous Block or CALL Statement ............. .............. 7-5

Dynamic SQL Statement is Anonymous Block or CALL Statement................................... 7-6

DBMS_SQL Package ............................................................................................................................... 7-6

DBMS_SQL.TO_REFCURSOR Function .............. .............. ............... ............... ............... ............... 7-7

DBMS_SQL.TO_CURSOR_NUMBER Function............................................................................ 7-8

SQL Injection ............................................................................................................................................ 7-9

SQL Injection Techniques .............. ................ .............. ............... ............... ............... ................ ..... 7-10

Statement Modification........................................................................................................... 7-10

Statement Injection ............. ............... ............... ............... ............... ............... ............... ........... 7-11

Data Type Conversion............................................................................................................. 7-13

Guarding Against SQL Injection................................................................................................... 7-14

Bind Arguments....................................................................................................................... 7-15

Validation Checks.................................................................................................................... 7-16

Explicit Format Models........................................................................................................... 7-17

8 PL/SQL Subprograms

Reasons to Use Subprograms................................................................................................................. 8-1

Nested, Package, and Standalone Stored Subprograms ................................................................... 8-2

Subprogram Invocations......................................................................................................................... 8-2

Subprogram Parts ..................................................................................................................................... 8-3Additional Parts for Functions......................................................................................................... 8-4

RETURN Statement ............. ............... ............... .............. ............... .............. ................ ............... ...... 8-5

RETURN Statement in Function............................................................................................... 8-6

RETURN Statement in Procedure ............... ............... ............... ............... ............... ................ . 8-7

RETURN Statement in Anonymous Block.............................................................................. 8-8

Forward Declaration ................................................................................................................................ 8-8

Subprogram Parameters.......................................................................................................................... 8-9

Formal and Actual Subprogram Parameters ............. .............. ............... ............... .............. .......... 8-9

Subprogram Parameter Passing Methods................................................................................... 8-11

Subprogram Parameter Modes ............. ............... ................ .............. ............... .............. .............. 8-11

Subprogram Parameter Aliasing .............. ............... ............... ............... .............. ................ ......... 8-15Subprogram Parameter Aliasing with Parameters Passed by Reference............... ......... 8-15

Subprogram Parameter Aliasing with Cursor Variable Parameters................................ 8-17

Default Values for IN Subprogram Parameters .............. ................ ............... ............... ............. 8-18

Positional, Named, and Mixed Notation for Actual Parameters .............. ................ .............. . 8-21

Subprogram Invocation Resolution .................................................................................................. 8-22

Overloaded Subprograms.................................................................................................................... 8-24

Formal Parameters that Differ Only in Numeric Data Type .............. ............... ............... ........ 8-25

8/2/2019 11g_plsql

12/740

xii

Subprograms that You Cannot Overload.................................................................................... 8-27

Subprogram Overload Errors........................................................................................................ 8-27

Recursive Subprograms ....................................................................................................................... 8-29

Subprogram Side Effects ..................................................................................................................... 8-30

PL/SQL Function Result Cache .......................................................................................................... 8-30

Enabling Result-Caching for a Function...................................................................................... 8-31

Developing Applications with Result-Cached Functions......................................................... 8-32Restrictions on Result-Cached Functions ............. ............... ................ ............... .............. ........... 8-33

Examples of Result-Cached Functions......................................................................................... 8-33

Result-Cached Application Configuration Parameters...................................................... 8-34

Result-Cached Recursive Function........................................................................................ 8-35

Advanced Result-Cached Function Topics .............. ................ .............. ............... ................ ...... 8-36

Rules for a Cache Hit............................................................................................................... 8-36

Result Cache Bypass................................................................................................................ 8-36

Making Result-Cached Functions Handle Session-Specific Settings .............. ............... .. 8-37

Making Result-Cached Functions Handle Session-Specific Application Contexts........ 8-38

Choosing Result-Caching Granularity.................................................................................. 8-38

Result Caches in Oracle RAC Environment......................................................................... 8-40Result Cache Management .............. ................ .............. ............... .............. ............... ............. 8-40

Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend.................... 8-41

PL/SQL Functions that SQL Statements Can Invoke ..................................................................... 8-42

Invoker's Rights and Definer's Rights (AUTHID Property) ........................................................ 8-42

Choosing AUTHID CURRENT_USER or AUTHID DEFINER................................................ 8-44

AUTHID and SQL Command SET ROLE ............. ................ .............. ............... .............. ........... 8-44

Need for Template Objects in IR Units ............. .............. ................ .............. ............... ............... . 8-44

Overriding Default Name Resolution in IR Units...................................................................... 8-44

IR Subprograms, Views, and Database Triggers........................................................................ 8-45

IR Database Links............................................................................................................................ 8-45

IR ADTs ............. ............... .............. .............. ................ .............. .............. ............... ............... .......... 8-45

IR Instance Methods .............. ............... .............. ............... ............... ............... ............... ................ 8-47

External Subprograms .......................................................................................................................... 8-48

9 PL/SQL Triggers

Overview of Triggers ............................................................................................................................... 9-1

Reasons to Use Triggers .......................................................................................................................... 9-2

DML Triggers ............................................................................................................................................ 9-3

Conditional Predicates for Detecting Triggering DML Statement .............. .............. ............... .. 9-4

Correlation Names and Pseudorecords.......................................................................................... 9-5

OBJECT_VALUE Pseudocolumn..................................................................................................... 9-9INSTEAD OF Triggers.................................................................................................................... 9-10

Compound DML Triggers .............. .............. ................ .............. ............... ............... ............... ...... 9-14

Compound DML Trigger Structure ............... .............. ............... ................ ............... ........... 9-15

Compound DML Trigger Restrictions.................................................................................. 9-15

Performance Benefit of Compound DML Triggers............................................................. 9-16

Using Compound DML Triggers with Bulk Insertion ............... .............. ............... ........... 9-16

Using Compound DML Triggers to Avoid Mutating-Table Error................................... 9-18

Triggers for Ensuring Referential Integrity................................................................................. 9-18

8/2/2019 11g_plsql

13/740

xiii

Foreign Key Trigger for Child Table..................................................................................... 9-20

UPDATE and DELETE RESTRICT Triggers for Parent Table........................................... 9-21

UPDATE and DELETE SET NULL Triggers for Parent Table ............... ............... ............ 9-22

DELETE CASCADE Trigger for Parent Table ............... ............... .............. .............. ........... 9-22

UPDATE CASCADE Trigger for Parent Table.................................................................... 9-23

Triggers for Complex Check Constraints............................................................................. 9-24

Triggers for Complex Security Authorizations ............... ............... ............... ............... ....... 9-25Triggers for Transparent Event Logging.............................................................................. 9-26

Triggers for Deriving Column Values .............. ................ ............... ............... ............... ....... 9-26

Triggers for Building Complex Updatable Views............................................................... 9-27

Triggers for Fine-Grained Access Control............................................................................ 9-30

System Triggers...................................................................................................................................... 9-30

SCHEMA Triggers ............. ............... .............. ............... ............... ............... .............. ............... ...... 9-31

DATABASE Triggers...................................................................................................................... 9-31

Subprograms Invoked by Triggers .................................................................................................... 9-32

Trigger Compilation, Invalidation, and Recompilation ................................................................ 9-32

Exception Handling in Triggers.......................................................................................................... 9-33

Trigger Design Guidelines .................................................................................................................. 9-35Trigger Restrictions ............................................................................................................................... 9-36

Trigger Size Restriction .............. .............. ............... ............... .............. ................ .............. ............ 9-36

Trigger LONG and LONG RAW Data Type Restrictions......................................................... 9-37

Mutating-Table Restriction............................................................................................................ 9-37

Order in Which Triggers Fire .............................................................................................................. 9-40

Trigger Enabling and Disabling ......................................................................................................... 9-41

Trigger Changing and Debugging ..................................................................................................... 9-42

Triggers and Oracle Database Data Transfer Utilities.................................................................... 9-42

Triggers for Publishing Events ........................................................................................................... 9-43

Event Attribute Functions.............................................................................................................. 9-44

Event Attribute Functions for Database Event Triggers ............... ............... .............. ............... 9-48

Event Attribute Functions for Client Event Triggers................................................................. 9-48

Views for Information About Triggers.............................................................................................. 9-52

10 PL/SQL Packages

What is a Package? ................................................................................................................................ 10-1

Reasons to Use Packages...................................................................................................................... 10-2

Package Specification ........................................................................................................................... 10-3

Appropriate Public Items............................................................................................................... 10-3

Creating Package Specifications ................ ................. ............... .............. ............... ............... ....... 10-4

Package Body ......................................................................................................................................... 10-5Package Instantiation and Initialization........................................................................................... 10-6

Package State .......................................................................................................................................... 10-7

SERIALLY_REUSABLE Packages ...................................................................................................... 10-7

Creating SERIALLY_REUSABLE Packages ............... ................ ............... .............. ............... ..... 10-8

SERIALLY_REUSABLE Package Work Unit ............... ............... .............. ................ ............... ... 10-8

Explicit Cursors in SERIALLY_REUSABLE Packages ............. ............... .............. ............... ..... 10-9

Package Writing Guidelines.............................................................................................................. 10-11

Package Example ................................................................................................................................. 10-12

8/2/2019 11g_plsql

14/740

xiv

How STANDARD Package Defines the PL/SQL Environment................................................. 10-15

11 PL/SQL Error Handling

Compile-Time Warnings ...................................................................................................................... 11-1

DBMS_WARNING Package ............................................................................................................... 11-3

Overview of Exception Handling....................................................................................................... 11-4

Advantages of Exceptions.............................................................................................................. 11-5

Guidelines for Avoiding and Handling Exceptions ............. ............... ............... .............. ......... 11-6

Predefined Exceptions .......................................................................................................................... 11-7

User-Defined Exceptions ..................................................................................................................... 11-8

Exception Declarations................................................................................................................... 11-8

Exception Scope............................................................................................................................... 11-8

Associating Exceptions with Numbers........................................................................................ 11-9

User-Defined Error Messages...................................................................................................... 11-10

Redeclaring Predefined Exceptions............................................................................................ 11-11

How Exceptions Are Raised .............................................................................................................. 11-12

How Exceptions Propagate ................................................................................................................ 11-13

Reraising an Exception ....................................................................................................................... 11-14

Exception Handlers ............................................................................................................................. 11-15

Exceptions Raised in Declarations.............................................................................................. 11-16

Exceptions Raised in Exception Handlers................................................................................. 11-17

Transferring Control to or from Exception Handlers.............................................................. 11-17

Error Code and Error Message Retrieval................................................................................... 11-17

Continuing Execution After an Exception Is Raised................................................................ 11-18

Retrying a Transaction ................................................................................................................. 11-19

Identifying Exception Locations With Locator Variables ............... .............. ............... ........... 11-20

Unhandled Exceptions ....................................................................................................................... 11-21

12 PL/SQL Optimization and Tuning

PL/SQL Optimizer................................................................................................................................. 12-1

Subprogram Inlining ............................................................................................................................ 12-2

PL/SQL Code to Consider Tuning ...................................................................................................... 12-3

Avoiding CPU Overhead in PL/SQL Code....................................................................................... 12-4

Make SQL Statements as Efficient as Possible ............... ................ .............. ............... ................ 12-4

Make Function Invocations as Efficient as Possible................................................................... 12-5

Make Loops as Efficient as Possible ............... .............. ................ .............. ............... ................ ... 12-6

Use SQL String Functions ............... .............. ................ .............. ............... ................ ............... ..... 12-6

Put Least Expensive Conditional Tests First............................................................................... 12-6

Minimize Implicit Data Type Conversion................................................................................... 12-6Avoid NUMBER Data Type and Constrained Subtypes........................................................... 12-7

Recommended Data Types for Integer Arithmetic............................................................. 12-7

Recommended Data Types for Floating-Point Arithmetic ............. ............... .............. ...... 12-7

Collecting Data About User-Defined Identifiers............................................................................ 12-7

Profiling and Tracing PL/SQL Programs .......................................................................................... 12-8

Profiler API: Package DBMS_PROFILER .............. .............. ................ .............. ............... ........... 12-8

Trace API: Package DBMS_TRACE.............................................................................................. 12-9

Reducing Loop Overhead with Bulk SQL ....................................................................................... 12-9

8/2/2019 11g_plsql

15/740

xv

Running One DML Statement Multiple Times (FORALL Statement) ............... ............... .... 12-10

Effect of FORALL Exceptions on Rollbacks....................................................................... 12-15

Handling FORALL Exceptions .............. ............... .............. ................ .............. ............... .... 12-16

Counting Rows Affected by FORALL ................ ............... .............. ................ ............... .... 12-18

Retrieving Query Results into Collections ................... .............. ............... .............. ............... ... 12-20

Examples of Bulk Fetching from a Cursor .............. ............... ............... ................ ............. 12-22

Limiting Rows for a Bulk FETCH Operation (LIMIT Clause)......................................... 12-25Retrieving DML Results Into a Collection (RETURNING INTO Clause) .............. ....... 12-26

Using FORALL and BULK COLLECT Together............................................................... 12-26

Host Arrays with Bulk Binds ............... .............. ................ .............. ............... .............. ....... 12-27

SELECT BULK COLLECT INTO Statements and Aliasing............................................. 12-28

Computation-Intensive PL/SQL Programs..................................................................................... 12-32

Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables ....... 12-33

Tuning PL/SQL Subprogram Invocations with NOCOPY Hint ................................................ 12-33

Compiling PL/SQL Units for Native Execution ............................................................................ 12-35

Determining Whether to Use PL/SQL Native Compilation .............. .............. ............... ....... 12-36

How PL/SQL Native Compilation Works................................................................................ 12-36

Dependencies, Invalidation, and Revalidation......................................................................... 12-37Setting Up a New Database for PL/SQL Native Compilation............................................... 12-37

Compiling the Entire Database for PL/SQL Native or Interpreted Compilation............... 12-37

Performing Multiple Transformations with Pipelined Table Functions.................................. 12-39

Overview of Pipelined Table Functions..................................................................................... 12-40

Writing a Pipelined Table Function............................................................................................ 12-41

Pipelined Table Functions for Transformations ................. .............. ............... ............... .......... 12-41

Returning Results from Pipelined Table Functions ............. ............... .............. .............. ......... 12-43

Pipelining Data Between PL/SQL Table Functions................................................................. 12-44

Optimizing Multiple Invocations of Pipelined Table Functions............................................ 12-44

Fetching from Results of Pipelined Table Functions .............. .............. ............... ............... ..... 12-44

Passing Data with Cursor Variables........................................................................................... 12-45

Performing DML Statements Inside Pipelined Table Functions............................................ 12-48

Performing DML Statements on Pipelined Table Functions ............... ............... ............... ..... 12-48

NO_DATA_NEEDED Exception................................................................................................ 12-49

Updating Large Tables in Parallel.................................................................................................... 12-51

13 PL/SQL Language Elements

Assignment Statement ......................................................................................................................... 13-3

AUTONOMOUS_TRANSACTION Pragma ................................................................................... 13-7

Block ........................................................................................................................................................ 13-9

Basic LOOP Statement ....................................................................................................................... 13-19CASE Statement................................................................................................................................... 13-21

CLOSE Statement ................................................................................................................................ 13-24

Collection Variable.............................................................................................................................. 13-26

Collection Method Invocation .......................................................................................................... 13-32

Comment............................................................................................................................................... 13-35

Constant ................................................................................................................................................ 13-37

CONTINUE Statement....................................................................................................................... 13-39

Cursor FOR LOOP Statement ........................................................................................................... 13-41

8/2/2019 11g_plsql

16/740

xvi

Cursor Variable .................................................................................................................................... 13-43

DELETE Statement Extension........................................................................................................... 13-46

EXCEPTION_INIT Pragma ............................................................................................................... 13-47

Exception............................................................................................................................................... 13-49

Exception Handler............................................................................................................................... 13-51

EXECUTE IMMEDIATE Statement ................................................................................................. 13-53

EXIT Statement .................................................................................................................................... 13-56Explicit Cursor ..................................................................................................................................... 13-58

Expression............................................................................................................................................. 13-62

FETCH Statement ................................................................................................................................ 13-72

FOR LOOP Statement ........................................................................................................................ 13-75

FORALL Statement ............................................................................................................................. 13-78

Formal Parameter................................................................................................................................. 13-81

Function................................................................................................................................................. 13-84

GOTO Statement................................................................................................................................. 13-88

IF Statement.......................................................................................................................................... 13-90

Implicit Cursor Attribute ................................................................................................................... 13-92

INLINE Pragma ................................................................................................................................... 13-95INSERT Statement Extension ........................................................................................................... 13-97

Named Cursor Attribute .................................................................................................................... 13-99

NULL Statement ................................................................................................................................ 13-102

OPEN Statement ................................................................................................................................ 13-103

OPEN FOR Statement ...................................................................................................................... 13-104

PIPE ROW Statement ....................................................................................................................... 13-107

Procedure ............................................................................................................................................ 13-108

RAISE Statement ............................................................................................................................... 13-110

Record Variable.................................................................................................................................. 13-111

RESTRICT_REFERENCES Pragma ............................................................................................... 13-114

RETURN Statement .......................................................................................................................... 13-116

RETURNING INTO Clause ............................................................................................................ 13-118

%ROWTYPE Attribute ..................................................................................................................... 13-121

Scalar Variable ................................................................................................................................... 13-123

SELECT INTO Statement ................................................................................................................ 13-125

SERIALLY_REUSABLE Pragma ..................................................................................................... 13-130

SQLCODE Function ......................................................................................................................... 13-131

SQLERRM Function ......................................................................................................................... 13-132

%TYPE Attribute ............................................................................................................................... 13-134

UPDATE Statement Extensions ...................................................................................................... 13-136

WHILE LOOP Statement ................................................................................................................. 13-138

14 SQL Statements for Stored PL/SQL Units

ALTER FUNCTION Statement........................................................................................................... 14-3

ALTER LIBRARY Statement ............................................................................................................... 14-6

ALTER PACKAGE Statement ............................................................................................................. 14-8

ALTER PROCEDURE Statement ..................................................................................................... 14-11

ALTER TRIGGER Statement ............................................................................................................ 14-14

ALTER TYPE Statement ..................................................................................................................... 14-17

8/2/2019 11g_plsql

17/740

xvii

CREATE FUNCTION Statement ...................................................................................................... 14-32

CREATE LIBRARY Statement .......................................................................................................... 14-41

CREATE PACKAGE Statement ........................................................................................................ 14-43

CREATE PACKAGE BODY Statement ........................................................................................... 14-46

CREATE PROCEDURE Statement................................................................................................... 14-50

CREATE TRIGGER Statement ......................................................................................................... 14-54

CREATE TYPE Statement .................................................................................................................. 14-68CREATE TYPE BODY Statement ..................................................................................................... 14-85

DROP FUNCTION Statement .......................................................................................................... 14-90

DROP LIBRARY Statement .............................................................................................................. 14-92

DROP PACKAGE Statement ............................................................................................................ 14-93

DROP PROCEDURE Statement....................................................................................................... 14-95

DROP TRIGGER Statement ............................................................................................................. 14-97

DROP TYPE Statement ...................................................................................................................... 14-98

DROP TYPE BODY Statement ....................................................................................................... 14-100

A PL/SQL Source Code Wrapping

Overview of Wrapping ........................................................................................................................... A-1

Guidelines for Wrapping ....................................................................................................................... A-1

Limitations of Wrapping ........................................................................................................................ A-2

Wrapping PL/SQL Code with wrap Utility ........................................................................................ A-2

Input and Output Files for the PL/SQL wrap Utility.................................................................. A-3

Running the wrap Utility................................................................................................................. A-3

Limitations of the wrap Utility........................................................................................................ A-4

Wrapping PL/QL Code with DBMS_DDL Subprograms................................................................ A-4

DBMS_DDL.CREATE_WRAPPED Procedure ............. ............... ............... .............. .............. ...... A-5

Limitation of the DBMS_DDL.WRAP Function ............. ............... .............. ................ .............. ... A-6

B PL/SQL Name Resolution

What is Name Resolution? .................................................................................................................... B-1

Name Resolution............................................................................................................................... B-2

Examples of Qualified Names and Dot Notation ............................................................................. B-4

How Name Resolution Differs in PL/SQL and SQL ........................................................................ B-5

What is Capture?...................................................................................................................................... B-6

Inner Capture..................................................................................................................................... B-6

Same-Scope Capture......................................................................................................................... B-7

Outer Capture.................................................................................................................................... B-7

Avoiding Inner Capture in DML Statements .................................................................................... B-8

Qualifying References to Attributes and Methods....................................................................... B-8Qualifying References to Row Expressions................................................................................... B-9

8/2/2019 11g_plsql

18/740

xviii

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

E PL/SQL Predefined Data Types

Index

8/2/2019 11g_plsql

19/740

xix

List of Examples

11 PL/SQL Block Structure ............... .............. ................ .............. ............... .............. ................ .... 1-512 Processing Query Result Rows One at a Time ................. .............. ............... ............... .......... 1-921 Valid Case-Insensitive Reference to Quoted User-Defined Identifier ............... .............. ... 2-622 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier .............. ............... 2-723 Reserved Word as Quoted User-Defined Identifier .............. .............. ................ .............. .... 2-724 Neglecting Double Quotation Marks....................................................................................... 2-7

25 Neglecting Case-Sensitivity....................................................................................................... 2-826 Single-Line Comments ............... .............. ................ .............. ............... ............... ............... .... 2-1027 Multiline Comments................................................................................................................ 2-1028 Whitespace Characters Improving Source Code Readability .............. ............... .............. 2-1129 Scalar Variable Declarations................................................................................................... 2-12210 Constant Declarations ............. ................ .............. ............... ............... ............... ............... ...... 2-12211 Variable and Constant Declarations with Initial Values.................................................... 2-13212 Variable Initialized to NULL by Default ............. ................ ............... ............... ............... .... 2-13213 Variable Declaration with NOT NULL Constraint............................................................. 2-14214 Variables Initialized to NULL Values................................................................................... 2-14215 Declaring Variable of Same Type as Column .............. ............... ............... ............... ........... 2-15216 Declaring Variable of Same Type as Another Variable...................................................... 2-15217 Scope and Visibility of Identifiers ............. .............. ................ .............. ............... ............... .. 2-16218 Qualifying a Redeclared Global Identifier with a Block Label.......................................... 2-17219 Qualifying an Identifier with a Subprogram Name .............. ............... ............... ............... 2-17220 Duplicate Identifiers in Same Scope...................................................................................... 2-18221 Declaring the Same Identifier in Two Different Units........................................................ 2-19222 Label and Subprogram with Same Name in Same Scope.................................................. 2-19223 Block with Multiple and Duplicate Labels........................................................................... 2-20224 Assigning Values to Variables with Assignment Statement .............. ................ .............. . 2-21225 SELECT INTO Assigns Values to Scalar Variables............................................................. 2-22226 Assigning Values to Variables as Parameters of a Subprogram....................................... 2-22227 Assigning BOOLEAN Values ................................................................................................ 2-23228 Concatenation Operator.......................................................................................................... 2-24229 Concatenation Operator with NULL Operands.................................................................. 2-24

230 Controlling Evaluation Order with Parentheses................................................................. 2-25231 Expression with Nested Parentheses ............. ............... .............. ................ ............... ........... 2-25232 Improving Readability with Parentheses............................................................................. 2-26233 Operator Precedence .............. .............. ............... .............. ................ ............. ................ ......... 2-26234 AND Operator.......................................................................................................................... 2-27235 OR Operator.............................................................................................................................. 2-28236 NOT Operator .......................................................................................................................... 2-29237 NULL Value in Unequal Comparison ............. ............... ............... .............. ................ ......... 2-30238 NULL Value in Equal Comparison ............. ............... .............. ................ .............. ............... 2-30239 NOT NULL Equals NULL...................................................................................................... 2-31240 Changing Evaluation Order of Logical Operators.............................................................. 2-31241 Short-Circuit Evaluation ............... ................ .............. ............... ................ ............... .............. 2-31242 Relational Operators in Expressions ................ ............... ............... .............. .............. ........... 2-33

243 LIKE Operator in Expression .............. .............. ............... ................ ............. ................ ......... 2-34244 Escape Character in Pattern.................................................................................................... 2-35245 BETWEEN Operator in Expressions .............. .............. .............. ............... .............. .............. 2-35246 IN Operator in Expressions.................................................................................................... 2-36247 IN Operator with Sets with NULL Values........................................................................... 2-362