List of Examples

• 1-1 PL/SQL Block Structure
• 1-2 Declaring Variables in PL/SQL
• 1-3 Assigning Values to Variables with the Assignment Operator
• 1-4 Using SELECT INTO to Assign Values to Variables
• 1-5 Assigning Values to Variables as Parameters of a Subprogram
• 1-6 Processing Query Results in a LOOP
• 1-7 Using %ROWTYPE with an Explicit Cursor
• 1-8 Using the IF-THEN_ELSE and CASE Statement for Conditional Control
• 1-9 Using the FOR-LOOP
• 1-10 Using WHILE-LOOP for Control
• 1-11 Using the EXIT-WHEN Statement
• 1-12 Using the GOTO Statement
• 1-13 Creating a Stored Subprogram
• 1-14 Creating a Package and Package Body
• 1-15 Invoking a Procedure in a Package
• 1-16 Using a PL/SQL Collection Type
• 1-17 Declaring a Record Type
• 1-18 Defining an Object Type
• 1-19 Techniques for Invoking a Standalone Procedure from SQL*Plus
• 1-20 Creating a Database Trigger
• 2-1 NUMBER Literals
• 2-2 Using BINARY_FLOAT and BINARY_DOUBLE
• 2-3 Using DateTime Literals
• 2-4 Using Single-Line Comments
• 2-5 Using Multi-Line Comments
• 2-6 Using %TYPE with the Datatype of a Variable
• 2-7 Using %TYPE with Table Columns
• 2-8 Using %ROWTYPE with Table Rows
• 2-9 Using the %ROWTYPE Attribute
• 2-10 Assigning Values to a Record with a %ROWTYPE Declaration
• 2-11 Using an Alias for Column Names
• 2-12 Errors with Duplicate Identifiers in Same Scope
• 2-13 Case Sensitivity of Identifiers
• 2-14 Using a Block Label for Name Resolution
• 2-15 Using a Subprogram Name for Name Resolution
• 2-16 Scope Rules
• 2-17 Using a Label Qualifier with Identifiers
• 2-18 Using Subprogram Qualifier with Identifiers
• 2-19 PL/SQL Block Using Multiple and Duplicate Labels
• 2-20 Initialization of Variables and Constants
• 2-21 Assigning BOOLEAN Values
• 2-22 Assigning a Query Result to a Variable
• 2-23 Short-Circuit Evaluation
• 2-24 Using Comparison Operators
• 2-25 Using BOOLEAN Variables in Conditional Tests
• 2-26 Using the WHEN Clause with a CASE Statement
• 2-27 Using a Search Condition with a CASE Statement
• 2-28 Using NULLs in Comparisons
• 2-29 Using the Function DECODE
• 2-30 Using the Function NVL
• 2-31 Using the Function REPLACE
• 2-32 Using Static Constants
• 2-33 Using DBMS_DB_VERSION Constants
• 2-34 Using Conditional Compilation with Database Versions
• 2-35 Using ALTER PROCEDURE to Set PLSQL_CCFLAGS
• 2-36 Using PRINT_POST_PROCESSED_SOURCE to Display Source Code
• 3-1 Comparing Two CHAR Values
• 3-2 Comparing Two VARCHAR2 Values
• 3-3 Comparing CHAR Value and VARCHAR2 Value
• 3-4 Assigning a Literal Value to a TIMESTAMP Variable
• 3-5 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
• 3-6 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable
• 3-7 Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE
• 3-8 Assigning Literals to an INTERVAL YEAR TO MONTH Variable
• 3-9 Assigning Literals to an INTERVAL DAY TO SECOND Variable
• 3-10 Using Ranges with Subtypes
• 3-11 Type Compatibility with the NUMBER Datatype
• 3-12 Constraints Inherited by Subprograms
• 3-13 Default Value with Subtypes
• 3-14 Using SUBTYPE with %TYPE and %ROWTYPE
• 3-15 Implicit Conversion
• 4-1 Using a Simple IF-THEN Statement
• 4-2 Using a Simple IF-THEN-ELSE Statement
• 4-3 Nested IF Statements
• 4-4 Using the IF-THEN-ELSEIF Statement
• 4-5 Extended IF-THEN Statement
• 4-6 Using the CASE-WHEN Statement
• 4-7 Using the Searched CASE Statement
• 4-8 Using an EXIT Statement
• 4-9 Using an EXIT-WHEN Statement
• 4-10 Using a CONTINUE Statement
• 4-11 Using a CONTINUE-WHEN Statement
• 4-12 Using Labeled Loops
• 4-13 Simple FOR-LOOP Statement
• 4-14 Reverse FOR-LOOP Statement
• 4-15 Changing the Increment of the Counter in a FOR-LOOP Statement
• 4-16 Specifying a LOOP Range at Run Time
• 4-17 Scope of the LOOP Counter Variable
• 4-18 Using a Label for Referencing Variables Outside a Loop
• 4-19 Using Labels on Loops for Referencing
• 4-20 Using EXIT in a LOOP
• 4-21 Using EXIT with a Label in a LOOP
• 4-22 Using a Simple GOTO Statement
• 4-23 Using a NULL Statement to Allow a GOTO to a Label
• 4-24 Using a GOTO Statement to Branch an Enclosing Block
• 4-25 Using the NULL Statement to Show No Action
• 4-26 Using NULL as a Placeholder When Creating a Subprogram
• 5-1 Declaring Collection Types
• 5-2 Declaring an Associative Array
• 5-3 Declaring Nested Tables, Varrays, and Associative Arrays
• 5-4 Declaring Collections with %TYPE
• 5-5 Declaring a Procedure Parameter as a Nested Table
• 5-6 Invoking a Procedure with a Nested Table Parameter
• 5-7 Specifying Collection Element Types with %TYPE and %ROWTYPE
• 5-8 VARRAY of Records
• 5-9 NOT NULL Constraint on Collection Elements
• 5-10 Constructor for a Nested Table
• 5-11 Constructor for a Varray
• 5-12 Collection Constructor Including Null Elements
• 5-13 Combining Collection Declaration and Constructor
• 5-14 Empty Varray Constructor
• 5-15 Referencing a Nested Table Element
• 5-16 Referencing an Element of an Associative Array
• 5-17 Datatype Compatibility for Collection Assignment
• 5-18 Assigning a Null Value to a Nested Table
• 5-19 Assigning Nested Tables with Set Operators
• 5-20 Assigning Values to VARRAYs with Complex Datatypes
• 5-21 Assigning Values to Tables with Complex Datatypes
• 5-22 Checking if a Collection Is Null
• 5-23 Comparing Two Nested Tables
• 5-24 Comparing Nested Tables with Set Operators
• 5-25 Multilevel VARRAY
• 5-26 Multilevel Nested Table
• 5-27 Multilevel Associative Array
• 5-28 Checking Whether a Collection Element EXISTS
• 5-29 Counting Collection Elements with COUNT
• 5-30 Checking the Maximum Size of a Collection with LIMIT
• 5-31 Using FIRST and LAST with a Collection
• 5-32 Using PRIOR and NEXT to Access Collection Elements
• 5-33 Using NEXT to Access Elements of a Nested Table
• 5-34 Using EXTEND to Increase the Size of a Collection
• 5-35 Using TRIM to Decrease the Size of a Collection
• 5-36 Using TRIM on Deleted Elements
• 5-37 Using the DELETE Method on a Collection
• 5-38 Collection Exceptions
• 5-39 How Invalid Subscripts are Handled with DELETE(n)
• 5-40 Incompatibility Between Package and Local Collection Types
• 5-41 Declaring and Initializing a Simple Record Type
• 5-42 Declaring and Initializing Record Types
• 5-43 Using %ROWTYPE to Declare a Record
• 5-44 Returning a Record from a Function
• 5-45 Using a Record as Parameter to a Procedure
• 5-46 Declaring a Nested Record
• 5-47 Assigning Default Values to a Record
• 5-48 Assigning All the Fields of a Record in One Statement
• 5-49 Using SELECT INTO to Assign Values in a Record
• 5-50 Inserting a PL/SQL Record Using %ROWTYPE
• 5-51 Updating a Row Using a Record
• 5-52 Using the RETURNING INTO Clause with a Record
• 5-53 Using BULK COLLECT with a SELECT INTO Statement
• 6-1 Data Manipulation with PL/SQL
• 6-2 Checking SQL%ROWCOUNT After an UPDATE
• 6-3 Substituting PL/SQL Variables
• 6-4 Invoking the SQL COUNT Function in PL/SQL
• 6-5 Using CURRVAL and NEXTVAL
• 6-6 Using ROWNUM
• 6-7 Using SQL%FOUND
• 6-8 Using SQL%ROWCOUNT
• 6-9 Declaring a Cursor
• 6-10 Fetching with a Cursor
• 6-11 Referencing PL/SQL Variables Within Its Scope
• 6-12 Fetching the Same Cursor Into Different Variables
• 6-13 Fetching Bulk Data with a Cursor
• 6-14 Using %FOUND
• 6-15 Using %ISOPEN
• 6-16 Using %NOTFOUND
• 6-17 Using %ROWCOUNT
• 6-18 Using an Alias For Expressions in a Query
• 6-19 Using a Subquery in a Cursor
• 6-20 Using a Subquery in a FROM Clause
• 6-21 Using a Correlated Subquery
• 6-22 Passing Parameters to a Cursor FOR Loop
• 6-23 Passing Parameters to Explicit Cursors
• 6-24 Cursor Variable Returning a %ROWTYPE Variable
• 6-25 Using the %ROWTYPE Attribute to Provide the Datatype
• 6-26 Cursor Variable Returning a Record Type
• 6-27 Passing a REF CURSOR as a Parameter
• 6-28 Checking If a Cursor Variable is Open
• 6-29 Stored Procedure to Open a Ref Cursor
• 6-30 Stored Procedure to Open Ref Cursors with Different Queries
• 6-31 Cursor Variable with Different Return Types
• 6-32 Fetching from a Cursor Variable into a Record
• 6-33 Fetching from a Cursor Variable into Collections
• 6-34 Declaration of Cursor Variables in a Package
• 6-35 Using a Cursor Expression
• 6-36 Using COMMIT with the WRITE Clause
• 6-37 Using ROLLBACK
• 6-38 Using SAVEPOINT with ROLLBACK
• 6-39 Reusing a SAVEPOINT with ROLLBACK
• 6-40 Using SET TRANSACTION to Begin a Read-only Transaction
• 6-41 Using CURRENT OF to Update the Latest Row Fetched from a Cursor
• 6-42 Fetching Across COMMITs Using ROWID
• 6-43 Declaring an Autonomous Function in a Package
• 6-44 Declaring an Autonomous Standalone Procedure
• 6-45 Declaring an Autonomous PL/SQL Block
• 6-46 Declaring an Autonomous Trigger
• 6-47 Using Autonomous Triggers
• 6-48 Invoking an Autonomous Function
• 7-1 Invoking a Subprogram from a Dynamic PL/SQL Block
• 7-2 Unsupported Datatype in Native Dynamic SQL
• 7-3 Uninitialized Variable for NULL in USING Clause
• 7-4 Native Dynamic SQL with OPEN-FOR, FETCH, and CLOSE Statements
• 7-5 Repeated Placeholder Names in Dynamic PL/SQL Block
• 7-6 Switching from DBMS_SQL Package to Native Dynamic SQL
• 7-7 Switching from Native Dynamic SQL to DBMS_SQL Package
• 7-8 Setup for SQL Injection Examples
• 7-9 Password-Checking Procedure Vulnerable to Statement Modification
• 7-10 Password-Checking Procedure Vulnerable to Statement Injection
• 7-11 Password-Checking Procedure Immune to SQL Injection
• 7-12 Dynamic SQL
• 7-13 Using DBMS_ASSERT.ENQUOTE_LITERAL When Validating User Input
• 8-1 Simple PL/SQL Procedure
• 8-2 Simple PL/SQL Function
• 8-3 Forward Declaration for a Nested Subprogram
• 8-4 Formal Parameters and Actual Parameters
• 8-5 Subprogram Calls Using Positional, Named, and Mixed Notation
• 8-6 Using OUT Mode
• 8-7 Procedure with Default Parameter Values
• 8-8 Formal Parameter with Expression as Default Value
• 8-9 Overloading a Subprogram Name
• 8-10 Package Specification with Overloading Violation that Causes Compile-Time Error
• 8-11 Package Specification with Overloading Violation that Compiles Without Error
• 8-12 Invocation of Improperly Overloaded Subprogram
• 8-13 Package Specification Without Overloading Violations
• 8-14 Improper Invocation of Properly Overloaded Subprogram
• 8-15 Resolving PL/SQL Procedure Names
• 8-16 Specifying Invoker's Rights with a Procedure
• 8-17 Resolving External References in IR Subprogram
• 8-18 Inlined External Reference to Constant in IR Subprogram
• 8-19 Creating an Object Type with AUTHID CURRENT USER
• 8-20 Invoking an IR Instance Methods
• 8-21 Invoking an External Procedure from PL/SQL
• 8-22 Invoking a Java Function from PL/SQL
• 8-23 RESTRICT_REFERENCES Pragma
• 8-24 Aliasing from Passing Global Variable with NOCOPY Hint
• 8-25 Aliasing Passing Same Parameter Multiple Times
• 8-26 Aliasing from Assigning Cursor Variables to Same Work Area
• 8-27 Declaration and Definition of Result-Cached Function
• 8-28 Result-Cached Function that Returns Configuration Parameter Setting
• 8-29 Result-Cached Function that Depends on Session-Specific Application Context
• 8-30 Caching One Name at a Time (Finer Granularity)
• 8-31 Caching Translated Names One Language at a Time (Coarser Granularity)
• 9-1 CREATE TRIGGER Statement
• 9-2 Compound Trigger
• 9-3 Compound Trigger Records Changes to One Table in Another Table
• 9-4 Compound Trigger that Avoids Mutating-Table Error
• 10-1 A Simple Package Specification Without a Body
• 10-2 Matching Package Specifications and Bodies
• 10-3 Creating the emp_admin Package
• 10-4 Using PUT_LINE in the DBMS_OUTPUT Package
• 10-5 Separating Cursor Specifications with Packages
• 11-1 Run-Time Error Handling
• 11-2 Managing Multiple Errors with a Single Exception Handler
• 11-3 Scope of PL/SQL Exceptions
• 11-4 Using PRAGMA EXCEPTION_INIT
• 11-5 Raising an Application Error with raise_application_error
• 11-6 Using RAISE to Force a User-Defined Exception
• 11-7 Using RAISE to Force a Pre-Defined Exception
• 11-8 Scope of an Exception
• 11-9 Reraising a PL/SQL Exception
• 11-10 Raising an Exception in a Declaration
• 11-11 Displaying SQLCODE and SQLERRM
• 11-12 Continuing After an Exception
• 11-13 Retrying a Transaction After an Exception
• 11-14 Using a Locator Variable to Identify the Location of an Exception
• 11-15 Controlling the Display of PL/SQL Warnings
• 11-16 Using the DBMS_WARNING Package to Display Warnings
• 12-1 Nesting a Query to Improve Performance
• 12-2 Issuing DELETE Statements in a Loop
• 12-3 Issuing INSERT Statements in a Loop
• 12-4 Using FORALL with Part of a Collection
• 12-5 Using FORALL with Nonconsecutive Index Values
• 12-6 Using Rollbacks with FORALL
• 12-7 Using %BULK_ROWCOUNT with the FORALL Statement
• 12-8 Counting Rows Affected by FORALL with %BULK_ROWCOUNT
• 12-9 Bulk Operation that Continues Despite Exceptions
• 12-10 Retrieving Query Results with BULK COLLECT
• 12-11 Using the Pseudocolumn ROWNUM to Limit Query Results
• 12-12 Bulk-Fetching from a Cursor Into One or More Collections
• 12-13 Bulk-Fetching from a Cursor Into a Collection of Records
• 12-14 Using LIMIT to Control the Number of Rows In a BULK COLLECT
• 12-15 Using BULK COLLECT with the RETURNING INTO Clause
• 12-16 Using FORALL with BULK COLLECT
• 12-17 Using NOCOPY with Parameters
• 12-18 Assigning the Result of a Table Function
• 12-19 Using a Pipelined Table Function For a Transformation
• 12-20 Using Multiple REF CURSOR Input Variables
• 12-21 Using a Pipelined Table Function as an Aggregate Function
• 13-1 Specifying that a Subprogram Is To Be Inlined
• 13-2 Specifying that an Overloaded Subprogram Is To Be Inlined
• 13-3 Specifying that a Subprogram Is Not To Be Inlined
• 13-4 Applying Two INLINE Pragmas to the Same Subprogram
• 13-5 Creating a Serially Reusable Package
• A-1 Using the create_wrapped Procedure to Wrap a Package
• B-1 Resolving Global and Local Variable Names
• B-2 Using the Dot Notation to Qualify Names