2/99

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 Text 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 Redeclared Global Identifier with Block Label
• 2-19 Qualifying Identifier with Subprogram Name
• 2-20 Duplicate Identifiers in Same Scope
• 2-21 Declaring Same Identifier in 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 Assigning Value to Variable with SELECT INTO Statement
• 2-26 Assigning Value to Variable as IN OUT Subprogram Parameter
• 2-27 Assigning Value to BOOLEAN Variable
• 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 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
• 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 ... 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 Textsource text
• 3-1 CHAR and VARCHAR2 Blank-Padding Difference
• 3-2 Printing BOOLEAN Values
• 3-3 PLS_INTEGER Calculation Raises Overflow Exception
• 3-4 Preventing Example 3-3 Overflow
• 3-5 Violating Constraint of SIMPLE_INTEGER Subtype
• 3-6 User-Defined Unconstrained Subtypes Show Intended Use
• 3-7 User-Defined Constrained Subtype 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 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 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements
• 4-13 CONTINUE Statement in Basic LOOP Statement
• 4-14 CONTINUE WHEN Statement in Basic LOOP Statement
• 4-15 FOR LOOP Statements
• 4-16 Reverse FOR LOOP Statements
• 4-17 Simulating STEP Clause in FOR LOOP Statement
• 4-18 FOR LOOP Statement Tries to Change Index Value
• 4-19 Outside Statement References FOR LOOP Statement Index
• 4-20 FOR LOOP Statement Index with Same Name as Variable
• 4-21 FOR LOOP Statement References Variable with Same Name as Index
• 4-22 Nested FOR LOOP Statements with Same Index Name
• 4-23 FOR LOOP Statement Bounds
• 4-24 Specifying FOR LOOP Statement Bounds at Run Time
• 4-25 EXIT WHEN Statement in FOR LOOP Statement
• 4-26 EXIT WHEN Statement in Inner FOR LOOP Statement
• 4-27 CONTINUE WHEN Statement in Inner FOR LOOP Statement
• 4-28 WHILE LOOP Statements
• 4-29 GOTO Statement
• 4-30 Incorrect Label Placement
• 4-31 GOTO Statement Goes to Labeled NULL Statement
• 4-32 GOTO Statement Transfers Control to Enclosing Block
• 4-33 GOTO Statement Cannot Transfer Control into IF Statement
• 4-34 NULL Statement Showing No Action
• 4-35 NULL Statement as Placeholder During Subprogram Creation
• 4-36 NULL Statement in ELSE Clause of Simple CASE Statement
• 5-1 Associative Array Indexed by String
• 5-2 Function Returns Associative Array Indexed by PLS_INTEGER
• 5-3 Declaring Associative Array Constant
• 5-4 Varray (Variable-Size Array)
• 5-5 Nested Table of Local Type
• 5-6 Nested Table of Standalone Type
• 5-7 Initializing Collection (Varray) Variable to Empty
• 5-8 Data Type Compatibility for Collection Assignment
• 5-9 Assigning Null Value to Nested Table Variable
• 5-10 Assigning Set Operation Results to Nested Table Variable
• 5-11 Two-Dimensional Varray (Varray of Varrays)
• 5-12 Nested Tables of Nested Tables and Varrays of Integers
• 5-13 Nested Tables of Associative Arrays and Varrays of Strings
• 5-14 Comparing Varray and Nested Table Variables to NULL
• 5-15 Comparing Nested Tables for Equality and Inequality
• 5-16 Comparing Nested Tables with SQL Multiset Conditions
• 5-17 DELETE Method with Nested Table
• 5-18 DELETE Method with Associative Array Indexed by String
• 5-19 TRIM Method with Nested Table
• 5-20 EXTEND Method with Nested Table
• 5-21 EXISTS Method with Nested Table
• 5-22 FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER
• 5-23 FIRST and LAST Values for Associative Array Indexed by String
• 5-24 Printing Varray with FIRST and LAST in FOR LOOP
• 5-25 Printing Nested Table with FIRST and LAST in FOR LOOP
• 5-26 COUNT and LAST Values for Varray
• 5-27 COUNT and LAST Values for Nested Table
• 5-28 LIMIT and COUNT Values for Different Collection Types
• 5-29 PRIOR and NEXT Methods
• 5-30 Printing Elements of Sparse Nested Table
• 5-31 Identically Defined Package and Local Collection Types
• 5-32 Identically Defined Package and Standalone Collection Types
• 5-33 Declaring Record Constant
• 5-34 RECORD Type Definition and Variable Declarations
• 5-35 RECORD Type with RECORD Field (Nested Record)
• 5-36 RECORD Type with Varray Field
• 5-37 Identically Defined Package and Local RECORD Types
• 5-38 %ROWTYPE Variable Represents Full Database Table Row
• 5-39 %ROWTYPE Variable Does Not Inherit Initial Values or Constraints
• 5-40 %ROWTYPE Variable Represents Partial Database Table Row
• 5-41 %ROWTYPE Variable Represents Join Row
• 5-42 Inserting %ROWTYPE Record into Table (Wrong)
• 5-43 Inserting %ROWTYPE Record into Table (Right)
• 5-44 Assigning Record to Another Record of Same RECORD Type
• 5-45 Assigning %ROWTYPE Record to RECORD Type Record
• 5-46 Assigning Nested Record to Another Record of Same RECORD Type
• 5-47 SELECT INTO Assigns Values to Record Variable
• 5-48 FETCH Assigns Values to Record that Function Returns
• 5-49 UPDATE Statement Assigns Values to Record Variable
• 5-50 Assigning NULL to Record Variable
• 5-51 Initializing Table by Inserting Record of Default Values
• 5-52 Updating Rows with 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 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 Virtual 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 Statement
• 6-19 Explicit Cursor FOR LOOP Statement
• 6-20 Passing Parameters to Explicit Cursor FOR LOOP Statement
• 6-21 Cursor FOR Loop References Virtual 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 Opening Cursor Variable for Chosen Query (Same Return Type)
• 6-32 Opening Cursor Variable for Chosen Query (Different Return Types)
• 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 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 Statement for Multiple Tables
• 6-42 FETCH with FOR UPDATE Cursor After COMMIT Statement
• 6-43 Simulating CURRENT OF Clause with ROWID Pseudocolumn
• 6-44 Declaring Autonomous Function in Package
• 6-45 Declaring Autonomous Standalone Procedure
• 6-46 Declaring Autonomous PL/SQL Block
• 6-47 Autonomous Trigger Logs INSERT Statements
• 6-48 Autonomous Trigger Uses Native Dynamic SQL for DDL
• 6-49 Invoking Autonomous Function
• 7-1 Invoking Subprogram from Dynamic PL/SQL Block
• 7-2 Unsupported Data Type in Native Dynamic SQL
• 7-3 Uninitialized Variable Represents 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 Variables 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 Where Not Every Execution Path Leads 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 Nested Subprograms Invoke Each Other
• 8-9 Formal Parameters and Actual Parameters
• 8-10 Actual Parameter Inherits Only NOT NULL from Subtype
• 8-11 Actual Parameter and Return Value Inherit Only Range From Subtype
• 8-12 Function Implicitly Converts Formal Parameter to Constrained Subtype
• 8-13 Avoiding Implicit Conversion of Actual Parameters
• 8-14 Parameter Values Before, During, and After Procedure Invocation
• 8-15 OUT and IN OUT Parameter Values After Unhandled Exception
• 8-16 OUT Formal Parameter of Record Type with Non-NULL Default Value
• 8-17 Aliasing from Global Variable as Actual Parameter
• 8-18 Aliasing from Same Actual Parameter for Multiple Formal Parameters
• 8-19 Aliasing from Cursor Variable Subprogram Parameters
• 8-20 Procedure with Default Parameter Values
• 8-21 Function Provides Default Parameter Value
• 8-22 Adding Subprogram Parameter Without Changing Existing Invocations
• 8-23 Equivalent Invocations with Different Notations in Anonymous Block
• 8-24 Equivalent Invocations with Different Notations in SELECT Statements
• 8-25 Resolving PL/SQL Procedure Names
• 8-26 Overloaded Subprogram
• 8-27 Overload Error Causes Compile-Time Error
• 8-28 Overload Error Compiles Successfully
• 8-29 Invoking Subprogram in Example 8-28 Causes Compile-Time Error
• 8-30 Correcting Overload Error in Example 8-28
• 8-31 Invoking Subprogram in Example 8-30
• 8-32 Package Specification Without Overload Errors
• 8-33 Improper Invocation of Properly Overloaded Subprogram
• 8-34 Implicit Conversion of Parameters Causes Overload Error
• 8-35 Recursive Function Returns n Factorial (n!)
• 8-36 Recursive Function Returns nth Fibonacci Number
• 8-37 Declaring and Defining Result-Cached Function
• 8-38 Result-Cached Function Returns Configuration Parameter Setting
• 8-39 Result-Cached Function Handles Session-Specific Settings
• 8-40 Result-Cached Function Handles Session-Specific Application Context
• 8-41 Caching One Name at a Time (Finer Granularity)
• 8-42 Caching Translated Names One Language at a Time (Coarser Granularity)
• 8-43 ADT for Use in Any Schema
• 8-44 Invoking IR Instance Method
• 8-45 PL/SQL Anonymous Block Invokes External Procedure
• 8-46 PL/SQL Standalone Procedure Invokes External Procedure
• 9-1 Trigger Uses Conditional Predicates to Detect Triggering Statement
• 9-2 Trigger Logs Changes to EMPLOYEES.SALARY
• 9-3 Conditional Trigger Prints Salary Change Information
• 9-4 Trigger Modifies LOB Columns
• 9-5 Trigger with REFERENCING Clause
• 9-6 Trigger References OBJECT_VALUE Pseudocolumn
• 9-7 INSTEAD OF Trigger
• 9-8 INSTEAD OF Trigger on Nested Table Column of View
• 9-9 Compound Trigger Logs Changes to One Table in Another Table
• 9-10 Compound Trigger Avoids 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 Trigger for Parent Table
• 9-14 DELETE CASCADE Trigger for Parent Table
• 9-15 UPDATE CASCADE Trigger for Parent Table
• 9-16 Trigger Checks Complex Constraints
• 9-17 Trigger Enforces Security Authorizations
• 9-18 Trigger Derives New Column Values
• 9-19 BEFORE Statement Trigger on Sample Schema HR
• 9-20 AFTER Statement Trigger on Database
• 9-21 Trigger Monitors Logons
• 9-22 INSTEAD OF CREATE Trigger on Schema
• 9-23 Trigger Invokes Java Subprogram
• 9-24 Trigger Cannot Handle Exception if Remote Database is Unavailable
• 9-25 Workaround for Example 9-24
• 9-26 Trigger Causes Mutating-Table Error
• 9-27 Update Cascade
• 9-28 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 Share Exception Handler
• 11-5 Naming Internally Defined Exception
• 11-6 Anonymous Block Handles ZERO_DIVIDE
• 11-7 Anonymous Block Avoids ZERO_DIVIDE
• 11-8 Redeclared Predefined Identifier
• 11-9 Declaring, Raising, and Handling User-Defined Exception
• 11-10 Explicitly Raising Predefined Exception
• 11-11 Reraising 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 Subprogram Is To Be Inlined
• 12-2 Specifying that Overloaded Subprogram Is To Be Inlined
• 12-3 Specifying that Subprogram Is Not To Be Inlined
• 12-4 PRAGMA INLINE ... 'NO' Overrides PRAGMA INLINE ... 'YES'
• 12-5 Nested Query Improves Performance
• 12-6 NOCOPY Subprogram Parameters
• 12-7 DELETE Statement in FOR LOOP Statement
• 12-8 DELETE Statement in FORALL Statement
• 12-9 Time Difference for INSERT Statement in FOR LOOP and FORALL Statements
• 12-10 FORALL Statement for Subset of Collection
• 12-11 FORALL Statements for Sparse Collection and Its Subsets
• 12-12 Handling FORALL Exceptions Immediately
• 12-13 Handling FORALL Exceptions After FORALL Statement Completes
• 12-14 Showing Number of Rows Affected by Each DELETE in FORALL
• 12-15 Showing Number of Rows Affected by Each INSERT SELECT in FORALL
• 12-16 Bulk-Selecting Two Database Columns into Two Nested Tables
• 12-17 Bulk-Selecting into Nested Table of Records
• 12-18 SELECT BULK COLLECT INTO Statement with Unexpected Results
• 12-19 Cursor Workaround for Example 12-18
• 12-20 Second Collection Workaround for Example 12-18
• 12-21 Limiting Bulk Selection with ROWNUM and SAMPLE
• 12-22 Bulk-Fetching into Two Nested Tables
• 12-23 Bulk-Fetching into Nested Table of Records
• 12-24 Limiting Bulk FETCH with LIMIT
• 12-25 Returning Deleted Rows in Two Nested Tables
• 12-26 DELETE with RETURN BULK COLLECT INTO in FORALL Statement
• 12-27 DELETE with RETURN BULK COLLECT INTO in FOR LOOP Statement
• 12-28 Anonymous Block Bulk-Binds Input Host Array
• 12-29 Creating and Invoking Pipelined Table Function
• 12-30 Pipelined Table Function Transforms Each Row to Two Rows
• 12-31 Fetching from Results of Pipelined Table Functions
• 12-32 Pipelined Table Function with Two Cursor Variable Parameters
• 12-33 Pipelined Table Function as Aggregate Function
• 12-34 Pipelined Table Function Does Not Handle NO_DATA_NEEDED
• 12-35 Pipelined Table Function Handles NO_DATA_NEEDED
• A-1 SQL File with Two Wrappable PL/SQL Units
• A-2 Wrapping File with PL/SQL Wrapper Utility
• A-3 Running Wrapped File and Viewing Wrapped PL/SQL Units
• A-4 Creating Wrapped Package Body with CREATE_WRAPPED Procedure
• A-5 Viewing Package with Wrapped Body and Invoking Package Procedure
• B-1 Qualified Names
• B-2 Variable Name Interpreted as Column Name Causes Unintended Result
• B-3 Fixing Example B-2 with Different Variable Name
• B-4 Fixing Example B-2 with Block Label
• B-5 Subprogram Name for Name Resolution
• B-6 Inner Capture of Column Reference
• B-7 Inner Capture of Attribute Reference
• B-8 Qualifying ADT Attribute References
• B-9 Qualifying References to Row Expressions