List of Examples

• 1-1 PL/SQL Block Structure
• 1-2 Processing Query Result Rows One at a Time
• 2-1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier
• 2-2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier
• 2-3 Reserved Word as Quoted User-Defined Identifier
• 2-4 Neglecting Double Quotation Marks
• 2-5 Neglecting Case-Sensitivity
• 2-6 Single-Line Comments
• 2-7 Multiline Comments
• 2-8 Whitespace Characters Improving Source Code Readability
• 2-9 Scalar Variable Declarations
• 2-10 Constant Declarations
• 2-11 Variable and Constant Declarations with Initial Values
• 2-12 Variable Initialized to NULL by Default
• 2-13 Variable Declaration with NOT NULL Constraint
• 2-14 Variables Initialized to NULL Values
• 2-15 Declaring Variable of Same Type as Column
• 2-16 Declaring Variable of Same Type as Another Variable
• 2-17 Scope and Visibility of Identifiers
• 2-18 Qualifying a Redeclared Global Identifier with a Block Label
• 2-19 Qualifying an Identifier with a Subprogram Name
• 2-20 Duplicate Identifiers in Same Scope
• 2-21 Declaring the Same Identifier in Two Different Units
• 2-22 Label and Subprogram with Same Name in Same Scope
• 2-23 Block with Multiple and Duplicate Labels
• 2-24 Assigning Values to Variables with Assignment Statement
• 2-25 SELECT INTO Assigns Values to Scalar Variables
• 2-26 Assigning Values to Variables as Parameters of a Subprogram
• 2-27 Assigning BOOLEAN Values
• 2-28 Concatenation Operator
• 2-29 Concatenation Operator with NULL Operands
• 2-30 Controlling Evaluation Order with Parentheses
• 2-31 Expression with Nested Parentheses
• 2-32 Improving Readability with Parentheses
• 2-33 Operator Precedence
• 2-34 Procedure that Prints BOOLEAN Variable
• 2-35 AND Operator
• 2-36 OR Operator
• 2-37 NOT Operator
• 2-38 NULL Value in Unequal Comparison
• 2-39 NULL Value in Equal Comparison
• 2-40 NOT NULL Equals NULL
• 2-41 Changing Evaluation Order of Logical Operators
• 2-42 Short-Circuit Evaluation
• 2-43 Relational Operators in Expressions
• 2-44 LIKE Operator in Expression
• 2-45 Escape Character in Pattern
• 2-46 BETWEEN Operator in Expressions
• 2-47 IN Operator in Expressions
• 2-48 IN Operator with Sets with NULL Values
• 2-49 Equivalent BOOLEAN Expressions as Conditions in Loops
• 2-50 Simple CASE Expression
• 2-51 Simple CASE Expression with WHEN NULL
• 2-52 Searched CASE Expression
• 2-53 Searched CASE Expression with WHEN condition IS NULL
• 2-54 Predefined Inquiry Directives $$PLSQL_LINE and $$PLSQL_UNIT
• 2-55 Displaying Values of PL/SQL Compilation Parameters
• 2-56 PLSQL_CCFLAGS Assigns Value to Itself
• 2-57 Static Constants
• 2-58 Code for Checking Database Version
• 2-59 Compiling Different Code for Different Database Versions
• 2-60 Displaying Post-Processed Source Code
• 3-1 CHAR and VARCHAR2 Blank-Padding Difference
• 3-2 Printing BOOLEAN Values
• 3-3 PLS_INTEGER Calculation that Raises Overflow Exception
• 3-4 Preventing the Overflow in Example 3-3
• 3-5 Violating Constraint of SIMPLE_INTEGER Subtype
• 3-6 User-Defined Unconstrained Subtypes that Show Intended Use
• 3-7 User-Defined Constrained Subtype that Detects Out-of-Range Values
• 3-8 Implicit Conversion Between Constrained Subtypes with Same Base Type
• 3-9 Implicit Conversion Between Subtypes with Base Types in Same Family
• 4-1 IF THEN Statement
• 4-2 IF THEN ELSE Statement
• 4-3 Nested IF THEN ELSE Statements
• 4-4 IF THEN ELSIF Statement
• 4-5 IF THEN ELSIF Statement that Simulates Simple CASE Statement
• 4-6 Simple CASE Statement
• 4-7 Searched CASE Statement
• 4-8 EXCEPTION Instead of ELSE Clause in CASE Statement
• 4-9 Basic LOOP Statement with EXIT Statement
• 4-10 Basic LOOP Statement with EXIT WHEN Statement
• 4-11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements
• 4-12 CONTINUE Statement in Basic LOOP Statement
• 4-13 CONTINUE WHEN Statement in Basic LOOP Statement
• 4-14 FOR LOOP Statements
• 4-15 Reverse FOR LOOP Statements
• 4-16 Simulating STEP Clause in FOR LOOP Statement
• 4-17 FOR LOOP Statement Tries to Change Index Value
• 4-18 Statement Outside FOR LOOP Tries to Reference Index
• 4-19 FOR LOOP Index with Same Name as Declared Variable
• 4-20 FOR LOOP References Declared Variable with Same Name as Index
• 4-21 Nested FOR LOOP Statements with Same Index Name
• 4-22 FOR LOOP Bounds
• 4-23 Specifying a LOOP Range at Run Time
• 4-24 EXIT WHEN Statement in FOR LOOP
• 4-25 EXIT WHEN Statement in Inner FOR LOOP Statement
• 4-26 CONTINUE WHEN Statement in Inner FOR LOOP Statement
• 4-27 WHILE LOOP Statements
• 4-28 GOTO Statement
• 4-29 Incorrect Label Placement
• 4-30 NULL Statement Allows GOTO to Label
• 4-31 GOTO Statement Transfers Control to Enclosing Block
• 4-32 GOTO Statement Cannot Transfer Control into IF Statement
• 4-33 NULL Statement Showing No Action
• 4-34 NULL Statement as Placeholder During Subprogram Creation
• 4-35 NULL Statement in WHEN OTHER Clause
• 5-1 Associative Array Indexed by String
• 5-2 Function Returns Associative Array Indexed by Integer
• 5-3 Varray (Variable-Size Array)
• 5-4 Nested Table of Local Type
• 5-5 Nested Table of Standalone Stored Type
• 5-6 Initializing Collection (Varray) Variable to Empty
• 5-7 Data Type Compatibility for Collection Assignment
• 5-8 Assigning a Null Value to a Nested Table Variable
• 5-9 Assigning Set Operation Results to Nested Table Variable
• 5-10 Two-Dimensional Varray (Varray of Varrays)
• 5-11 Nested Tables of Nested Tables and Varrays of Integers
• 5-12 Nested Tables of Associative Arrays and Varrays of Strings
• 5-13 Comparing Varray and Nested Table Variables to NULL
• 5-14 Comparing Nested Tables for Equality and Inequality
• 5-15 Comparing Nested Tables with SQL Multiset Conditions
• 5-16 DELETE Method with Nested Table
• 5-17 DELETE Method with Associative Array Indexed by String
• 5-18 TRIM Method with Nested Table
• 5-19 EXTEND Method with Nested Table
• 5-20 EXISTS Method with Nested Table
• 5-21 FIRST and LAST Values for Associative Array Indexed by Integer
• 5-22 FIRST and LAST Values for Associative Array Indexed by String
• 5-23 Printing Varray with FIRST and LAST in FOR LOOP
• 5-24 Printing Nested Table with FIRST and LAST in FOR LOOP
• 5-25 COUNT and LAST Values for Varray
• 5-26 COUNT and LAST Values for Nested Table
• 5-27 LIMIT and COUNT Values for Different Collection Types
• 5-28 PRIOR and NEXT Methods
• 5-29 Printing Elements of Sparse Nested Table
• 5-30 Identically Defined Package and Local Collection Types
• 5-31 Identically Declared Package and Standalone Stored Collection Types
• 5-32 RECORD Type Definition and Variable Declaration
• 5-33 RECORD Type with RECORD Field (Nested Record)
• 5-34 RECORD Type with Varray Field
• 5-35 Identically Defined Package and Local RECORD Types
• 5-36 %ROWTYPE Variable that Represents Full Database Table Row
• 5-37 %ROWTYPE Variable Does Not Inherit Initial Values or Constraints
• 5-38 %ROWTYPE Variable that Represents Partial Database Table Row
• 5-39 %ROWTYPE Variable that Represents Join Row
• 5-40 Assigning Record to Another of Same RECORD Type
• 5-41 Assigning %ROWTYPE Record to RECORD Type Record
• 5-42 Assigning Nested Record to Another of Same RECORD Type
• 5-43 SELECT INTO Assigns Values to Record Variable
• 5-44 FETCH Assigns Values to Record that Function Returns
• 5-45 UPDATE Statement Assigns Values to Record Variable
• 5-46 Initializing a Table by Inserting a Record of Default Values
• 5-47 Updating Rows with a Record
• 6-1 Static SQL Statements
• 6-2 CURRVAL and NEXTVAL Pseudocolumns
• 6-3 SQL%FOUND Implicit Cursor Attribute
• 6-4 SQL%ROWCOUNT Implicit Cursor Attribute
• 6-5 Explicit Cursor Declaration and Definition
• 6-6 FETCH Statements Inside LOOP Statements
• 6-7 Fetching the Same Explicit Cursor Into Different Variables
• 6-8 Variable in Explicit Cursor Query-No Result Set Change
• 6-9 Variable in Explicit Cursor Query-Result Set Change
• 6-10 Explicit Cursor with Calculated Column that Needs Alias
• 6-11 Explicit Cursor that Accepts Parameters
• 6-12 Cursor Parameters with Default Values
• 6-13 Adding Formal Parameter to Existing Cursor
• 6-14 %ISOPEN Explicit Cursor Attribute
• 6-15 %FOUND Explicit Cursor Attribute
• 6-16 %NOTFOUND Explicit Cursor Attribute
• 6-17 %ROWCOUNT Explicit Cursor Attribute
• 6-18 Implicit Cursor FOR Loop
• 6-19 Explicit Cursor FOR LOOP
• 6-20 Passing Parameters to an Explicit Cursor FOR LOOP
• 6-21 Cursor FOR Loop That References Calculated Columns
• 6-22 Subquery in FROM Clause of Parent Query
• 6-23 Correlated Subquery
• 6-24 Cursor Variable Declarations
• 6-25 Cursor Variable with User-Defined Return Type
• 6-26 Fetching Data with Cursor Variables
• 6-27 Fetching from Cursor Variable into Collections
• 6-28 Variable in Cursor Variable Query-No Result Set Change
• 6-29 Variable in Cursor Variable Query-Result Set Change
• 6-30 Procedure to Open Cursor Variable for One Query
• 6-31 Procedure to Open Cursor Variable for Chosen Query
• 6-32 Procedure to Open Cursor Variable for Chosen Query
• 6-33 Cursor Variable as Host Variable in Pro*C Client Program
• 6-34 Cursor Expression
• 6-35 COMMIT Statement with COMMENT and WRITE Clauses
• 6-36 ROLLBACK Statement
• 6-37 SAVEPOINT and ROLLBACK Statements
• 6-38 Reusing a SAVEPOINT with ROLLBACK
• 6-39 SET TRANSACTION Statement in Read-Only Transaction
• 6-40 FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement
• 6-41 SELECT FOR UPDATE with Multiple Tables
• 6-42 Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement
• 6-43 Simulating CURRENT OF Clause with ROWID Pseudocolumn
• 6-44 Declaring an Autonomous Function in a Package
• 6-45 Declaring an Autonomous Standalone Procedure
• 6-46 Declaring an Autonomous PL/SQL Block
• 6-47 Autonomous Trigger Logs INSERT Statements
• 6-48 Autonomous Trigger Using Native Dynamic SQL for DDL
• 6-49 Invoking an Autonomous Function
• 7-1 Invoking a Subprogram from a Dynamic PL/SQL Block
• 7-2 Unsupported Data Type 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 Procedure Vulnerable to Statement Modification
• 7-10 Procedure Vulnerable to Statement Injection
• 7-11 Procedure Vulnerable to SQL Injection Through Data Type Conversion
• 7-12 Bind Arguments Guarding Against SQL Injection
• 7-13 Validation Checks Guarding Against SQL Injection
• 7-14 Explicit Format Models Guarding Against SQL Injection
• 8-1 Declaring, Defining, and Invoking a Simple PL/SQL Procedure
• 8-2 Declaring, Defining, and Invoking a Simple PL/SQL Function
• 8-3 Execution Resumes After RETURN Statement in Function
• 8-4 Function with Execution Paths That Do Not Lead to RETURN Statement
• 8-5 Function Where Every Execution Path Leads to RETURN Statement
• 8-6 Execution Resumes After RETURN Statement in Procedure
• 8-7 Execution Resumes After RETURN Statement in Anonymous Block
• 8-8 Creating Nested Subprograms that Invoke Each Other
• 8-9 Formal Parameters and Actual Parameters
• 8-10 Parameter Inherits Only NOT NULL from Subtype
• 8-11 Avoiding Implicit Conversion of Actual Parameters
• 8-12 IN, OUT, and IN OUT Parameter Values Before, During, and After Procedure Invocation
• 8-13 OUT and IN OUT Parameter Values After Unhandled Exception
• 8-14 Subprogram Parameter Aliasing with Global Variable as Actual Parameter
• 8-15 Subprogram Parameter Aliasing with Same Actual Parameter for Multiple Formal Parameters
• 8-16 Subprogram Parameter Aliasing with Cursor Variable Parameters
• 8-17 Procedure with Default Parameter Values
• 8-18 Formal Parameter with Default Value Returned by Function
• 8-19 Adding Subprogram Parameter Without Changing Existing Invocations
• 8-20 Equivalent Invocations with Different Notations in Anonymous Block
• 8-21 Equivalent Invocations with Different Notations in SELECT Statements
• 8-22 Resolving PL/SQL Procedure Names
• 8-23 Overloaded Subprogram
• 8-24 Overload Error that Causes Compile-Time Error
• 8-25 Overload Error that Compiles Successfully
• 8-26 Invocation of Improperly Overloaded Subprogram
• 8-27 Properly Overloaded Subprogram
• 8-28 Invocation of Properly Overloaded Subprogram
• 8-29 Package Specification Without Overload Errors
• 8-30 Improper Invocation of Properly Overloaded Subprogram
• 8-31 Recursive Function that Returns n Factorial (n!)
• 8-32 Recursive Function that Returns nth Fibonacci Number
• 8-33 Declaration and Definition of Result-Cached Function
• 8-34 Result-Cached Function that Returns Configuration Parameter Setting
• 8-35 Function that Depends on Session-Specific Settings
• 8-36 Result-Cached Function that Depends on Session-Specific Application Context
• 8-37 Caching One Name at a Time (Finer Granularity)
• 8-38 Caching Translated Names One Language at a Time (Coarser Granularity)
• 8-39 Creating an ADT with AUTHID CURRENT USER
• 8-40 Invoking an IR Instance Method
• 8-41 PL/SQL Anonymous Block Invokes External Procedure
• 8-42 PL/SQL Standalone Stored Procedure Invokes External Procedure
• 9-1 Trigger that Uses Conditional Predicates to Detect Triggering Statement
• 9-2 Trigger that Logs Changes to EMPLOYEES.SALARY
• 9-3 Conditional Trigger that Prints Salary Change Information
• 9-4 Trigger that Modifies LOB Columns
• 9-5 REFERENCING Clause of CREATE TRIGGER Statement
• 9-6 Trigger with OBJECT_VALUE Pseudocolumn
• 9-7 INSTEAD OF Trigger
• 9-8 INSTEAD OF Trigger on Nested Table Column of View
• 9-9 Compound Trigger Records Changes to One Table in Another Table
• 9-10 Compound Trigger for Avoiding Mutating-Table Error
• 9-11 Foreign Key Trigger for Child Table
• 9-12 UPDATE and DELETE RESTRICT Trigger for Parent Table
• 9-13 UPDATE and DELETE SET NULL Triggers for Parent Table
• 9-14 DELETE CASCADE Trigger for Parent Table
• 9-15 UPDATE CASCADE Trigger for Parent Table
• 9-16 Trigger for Complex Check Constraints
• 9-17 Trigger for Enforcing Security
• 9-18 Trigger That Derives New Column Values for Table
• 9-19 BEFORE Statement Trigger on Sample Schema HR
• 9-20 AFTER Statement Trigger on Database
• 9-21 Trigger for Monitoring Logons
• 9-22 Trigger That Invokes Java Subprogram
• 9-23 Trigger that Cannot Handle Exception if Remote Database is Unavailable
• 9-24 Workaround for Example 9-23
• 9-25 Trigger that Causes Mutating-Table Error
• 9-26 Update Cascade
• 9-27 Viewing Information About Triggers
• 10-1 Simple Package Specification
• 10-2 Passing Associative Array to Standalone Subprogram
• 10-3 Matching Package Specification and Body
• 10-4 Creating SERIALLY_REUSABLE Packages
• 10-5 Effect of SERIALLY_REUSABLE Pragma
• 10-6 Cursor in SERIALLY_REUSABLE Package Open at Call Boundary
• 10-7 Separating Cursor Declaration and Definition in Package
• 10-8 Creating emp_admin Package
• 11-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
• 11-2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
• 11-3 Single Exception Handler for Multiple Exceptions
• 11-4 Locator Variables for Statements That Use Same Exception Handler
• 11-5 Naming Internally Defined Exception
• 11-6 Anonymous Block with Exception Handler for ZERO_DIVIDE
• 11-7 Anonymous Block that Avoids ZERO_DIVIDE
• 11-8 Redeclared Predefined Identifier
• 11-9 Declaring, Raising, and Handling a User-Defined Exception
• 11-10 Explicitly Raising a Predefined Exception
• 11-11 Reraising an Exception
• 11-12 Raising User-Defined Exception with RAISE_APPLICATION_ERROR
• 11-13 Exception That Propagates Beyond Scope is Handled
• 11-14 Exception That Propagates Beyond Scope is Not Handled
• 11-15 Exception Raised in Declaration is Not Handled
• 11-16 Exception Raised in Declaration is Handled by Enclosing Block
• 11-17 Exception Raised in Exception Handler is Not Handled
• 11-18 Exception Raised in Exception Handler is Handled by Invoker
• 11-19 Exception Raised in Exception Handler is Handled by Enclosing Block
• 11-20 Exception Raised in Exception Handler is Not Handled
• 11-21 Exception Raised in Exception Handler is Handled by Enclosing Block
• 11-22 Displaying SQLCODE and SQLERRM Values
• 11-23 Exception Handler Runs and Execution Ends
• 11-24 Exception Handler Runs and Execution Continues
• 11-25 Retrying Transaction After Handling Exception
• 12-1 Specifying that a Subprogram Is To Be Inlined
• 12-2 Specifying that an Overloaded Subprogram Is To Be Inlined
• 12-3 Specifying that a Subprogram Is Not To Be Inlined
• 12-4 Applying Two INLINE Pragmas to the Same Subprogram
• 12-5 Nested Query Improves Performance
• 12-6 NOCOPY with Parameters
• 12-7 Issuing DELETE Statements in a Loop
• 12-8 Issuing INSERT Statements in a Loop
• 12-9 FORALL Statement for Part of Collection
• 12-10 FORALL Statement for Nonconsecutive Index Values
• 12-11 Rollbacks with FORALL Statement
• 12-12 FORALL Statement and SQL%BULK_EXCEPTIONS
• 12-13 FORALL Statement and SQL%BULK_ROWCOUNT
• 12-14 Counting Rows Affected by FORALL with SQL%BULK_ROWCOUNT
• 12-15 Bulk-Selecting Two Database Columns into Two Nested Tables
• 12-16 Bulk-Selecting into Nested Table of Records
• 12-17 SELECT BULK COLLECT INTO Statement with Unexpected Results
• 12-18 Cursor Workaround for Example 12-17
• 12-19 Second Collection Workaround for Example 12-17
• 12-20 Limiting Query Results with Pseudocolumn ROWNUM
• 12-21 Bulk-Fetching into Two Nested Tables
• 12-22 Bulk-Fetching into Nested Table of Records
• 12-23 Controlling Number of BULK COLLECT Rows with LIMIT
• 12-24 Returning Deleted Rows in Two Nested Tables
• 12-25 FORALL with BULK COLLECT
• 12-26 Anonymous Block That Bulk-Binds Input Host Array
• 12-27 Associating a Cursor with a Dynamic SELECT Statement
• 12-28 Creating and Invoking a Pipelined Table Function
• 12-29 Pipelined Table Function for Transformation
• 12-30 Function with Two Cursor Variable Parameters
• 12-31 Pipelined Table Function as Aggregate Function
• 12-32 Pipelined Table Function that Does Not Handle NO_DATA_NEEDED
• 12-33 Pipelined Table Function that Handles NO_DATA_NEEDED
• A-1 Wrapping Package with DBMS_DDL.CREATE_WRAPPED Procedure
• B-1 Resolving Global and Local Variable Names
• B-2 Block Label for Name Resolution
• B-3 Subprogram Name for Name Resolution
• B-4 Dot Notation for Qualifying Names