List of Examples

• 1-1 PL/SQL Block Structure
• 1-2 Processing Query Result Rows One at a Time
• 2-1 Delimiter with Embedded Space
• 2-2 Whitespace Characters Improving Source Code Readability
• 2-3 Identifiers Not Separated by Space or Punctuation
• 2-4 Datetime and Interval Literals
• 2-5 Single-Line Comments
• 2-6 Multiline Comments
• 2-7 Variable Declarations
• 2-8 Constant Declarations
• 2-9 Variable and Constant Declarations with Initial Values
• 2-10 Variable Initialized to NULL by Default
• 2-11 Variable Declaration with NOT NULL Constraint
• 2-12 Variables Initialized to NULL Values
• 2-13 Declaring Variable of Same Type as Database Column
• 2-14 Declaring Variable of Same Type as Another Variable
• 2-15 Declaring Record that Represents Table Row
• 2-16 Declaring a Record that Represents a Subset of Table Columns
• 2-17 Declaring a Record that Represents a Row from a Join
• 2-18 Scope and Visibility of Identifiers
• 2-19 Qualifying a Redeclared Global Identifier with a Block Label
• 2-20 Qualifying an Identifier with a Subprogram Name
• 2-21 Duplicate Identifiers in Same Scope
• 2-22 Declaring the Same Identifier in Two Different Units
• 2-23 Label and Subprogram with Same Name in Same Scope
• 2-24 Block with Multiple and Duplicate Labels
• 2-25 Assigning Values to Variables with Assignment Statement
• 2-26 SELECT INTO Assigns Values to Variables
• 2-27 Assigning Values to Variables as Parameters of a Subprogram
• 2-28 Assigning BOOLEAN Values
• 2-29 Concatenation Operator
• 2-30 Concatenation Operator with NULL Operands
• 2-31 Controlling Evaluation Order with Parentheses
• 2-32 Expression with Nested Parentheses
• 2-33 Improving Readability with Parentheses
• 2-34 Operator Precedence
• 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 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 SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
• 3-6 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable
• 3-7 Correct Assignment to TIMESTAMP WITH LOCAL TIME ZONE
• 3-8 Incorrect Assignment to TIMESTAMP WITH LOCAL TIME ZONE
• 3-9 Assigning Literals to an INTERVAL YEAR TO MONTH Variable
• 3-10 Assigning Literals to an INTERVAL DAY TO SECOND Variable
• 3-11 Defining Subtypes
• 3-12 Declaring Variables of User-Defined Subtype
• 3-13 Ranges with Subtypes
• 3-14 Type Compatibility with the NUMBER Data Type
• 3-15 Assigning Initial Value to Subtype Variable
• 3-16 Subtype Constraints Inherited by Subprograms
• 3-17 Column Constraints Inherited by Subtypes
• 3-18 Implicit Conversion
• 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 WHILE LOOP Statements
• 4-15 FOR LOOP Statements
• 4-16 Reverse FOR LOOP Statements
• 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 Several Types of FOR LOOP Bounds
• 4-23 Changing the Increment of the Counter in a FOR LOOP Statement
• 4-24 Specifying a LOOP Range at Run Time
• 4-25 FOR LOOP with Lower Bound > Upper Bound
• 4-26 EXIT in FOR LOOP
• 4-27 EXIT with Label in FOR LOOP
• 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 Declaring and Populating Associative Array Indexed by String
• 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 Data Type 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 Data Types
• 5-21 Assigning Values to Tables with Complex Data Types
• 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 FIRST and LAST Methods
• 5-32 PRIOR and NEXT Methods
• 5-33 NEXT Method Accesses Elements of Nested Table
• 5-34 EXTEND Method
• 5-35 TRIM Method
• 5-36 TRIM Method on Deleted Elements
• 5-37 DELETE Method
• 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 %ROWTYPE in Record Declaration
• 5-44 Returning a Record from a Function
• 5-45 Record as Procedure Parameter
• 5-46 Nested Record Declaration
• 5-47 Assigning Initial Values to a Record
• 5-48 Assigning All the Fields of a Record in One Statement
• 5-49 Assigning a %ROWTYPE Record to a User-Defined Record
• 5-50 SELECT INTO Assigns Values to Record
• 5-51 Testing Records for Nullity and Equality
• 5-52 Initializing a Table by Inserting a Record of Default Values
• 5-53 Updating Rows with a Record
• 5-54 RETURNING INTO Clause with Record
• 5-55 BULK COLLECT with SELECT INTO Statement
• 6-1 Static SQL Statements
• 6-2 CURRVAL and NEXTVAL Pseudocolumns
• 6-3 SQL%FOUND Attribute
• 6-4 SQL%ROWCOUNT Attribute
• 6-5 Explicit Cursor Declaration
• 6-6 Opening an Explicit Cursor
• 6-7 Fetching with a Cursor
• 6-8 Referencing PL/SQL Variables in Its Scope
• 6-9 Fetching the Same Cursor Into Different Variables
• 6-10 Fetching Bulk Data with a Cursor
• 6-11 %ISOPEN Attribute
• 6-12 %FOUND Attribute
• 6-13 %NOTFOUND Attribute
• 6-14 %ROWCOUNT Attribute
• 6-15 Explicit Cursor with Calculated Column that Needs Alias
• 6-16 Passing Parameters to a Cursor FOR LOOP
• 6-17 Passing Parameters to Explicit Cursors
• 6-18 Implicit Cursor FOR Loop
• 6-19 Explicit Cursor FOR LOOP
• 6-20 Alias for Expressions in Query
• 6-21 Subqueries in Cursor Declarations
• 6-22 Subquery in FROM Clause
• 6-23 Correlated Subquery
• 6-24 Cursor Variable Declarations
• 6-25 Cursor Variables Returning %ROWTYPE Variables
• 6-26 %ROWTYPE Attribute Provides Data Type
• 6-27 Cursor Variable Returning a Record Type
• 6-28 Cursor Variable as Parameter
• 6-29 Opening Cursor Variable
• 6-30 Stored Procedure to Open a Cursor Variable
• 6-31 Stored Procedure to Open Cursor Variables with Different Queries
• 6-32 Cursor Variable with Different Return Types
• 6-33 Fetching from Cursor Variable into Record
• 6-34 Fetching from Cursor Variable into Collections
• 6-35 Cursor Variable as Host Variable
• 6-36 Reducing Network Traffic When Passing Host Variables
• 6-37 Declaration of Cursor Variables in a Package
• 6-38 Cursor Expression
• 6-39 COMMIT Statement with COMMENT and WRITE Clauses
• 6-40 ROLLBACK Statement
• 6-41 SAVEPOINT and ROLLBACK Statements
• 6-42 Reusing a SAVEPOINT with ROLLBACK
• 6-43 SET TRANSACTION Statement in Read-Only Transaction
• 6-44 FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement
• 6-45 SELECT FOR UPDATE with Multiple Tables
• 6-46 Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement
• 6-47 Simulating CURRENT OF Clause with ROWID Pseudocolumn
• 6-48 Declaring an Autonomous Function in a Package
• 6-49 Declaring an Autonomous Standalone Procedure
• 6-50 Declaring an Autonomous PL/SQL Block
• 6-51 Autonomous Trigger the Logs INSERT Statements
• 6-52 Autonomous Trigger Using Native Dynamic SQL for DDL
• 6-53 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 Creating Nested Subprograms that Invoke Each Other
• 8-4 Formal Parameters and Actual Parameters
• 8-5 Avoiding and Causing Implicit Conversion of Actual Parameters
• 8-6 Subprogram Parameter Mode OUT
• 8-7 Procedure with Initial Parameter Values
• 8-8 Formal Parameter with Expression as Initial Value
• 8-9 Subprogram Calls Using Positional, Named, and Mixed Notation
• 8-10 Overloaded Subprogram
• 8-11 Overload Error That Causes Compile-Time Error
• 8-12 Overload Error That Compiles Successfully
• 8-13 Invocation of Improperly Overloaded Subprogram
• 8-14 Properly Overloaded Subprogram
• 8-15 Invocation of Properly Overloaded Subprogram
• 8-16 Package Specification Without Overload Errors
• 8-17 Improper Invocation of Properly Overloaded Subprogram
• 8-18 Resolving PL/SQL Procedure Names
• 8-19 Creating an ADT with AUTHID CURRENT USER
• 8-20 Invoking an IR Instance Method
• 8-21 Invoking an External Procedure from PL/SQL
• 8-22 Invoking a Java Function from PL/SQL
• 8-23 Aliasing from Passing Global Variable with NOCOPY Hint
• 8-24 Aliasing Passing Same Parameter Multiple Times
• 8-25 Aliasing from Assigning Cursor Variables to Same Work Area
• 8-26 Declaration and Definition of Result-Cached Function
• 8-27 Result-Cached Function that Returns Configuration Parameter Setting
• 8-28 Function that Depends on Session-Specific Settings
• 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 INSTEAD OF Trigger
• 9-3 FOR EACH ROW Trigger
• 9-4 Compound Trigger
• 9-5 Compound Trigger Records Changes to One Table in Another Table
• 9-6 Compound Trigger for Avoiding Mutating-Table Error
• 9-7 Trigger for Monitoring Logons
• 9-8 Trigger That Invokes Java Subprogram
• 9-9 Trigger that Modifies LOB Columns
• 9-10 REFERENCING Option
• 9-11 Trigger with OBJECT_VALUE Pseudocolumn
• 9-12 Remote Exception Handling Failure
• 9-13 Workaround for Example 9-12
• 9-14 Row-Level Trigger Causes Mutating-Table Error
• 9-15 Statement-Level Trigger Avoids Mutating-Table Error
• 9-16 Update Cascade
• 9-17 Viewing Information About Triggers
• 9-18 Trigger for Auditing
• 9-19 Trigger for Auditing
• 9-20 Foreign Key Trigger for Child Table
• 9-21 UPDATE and DELETE RESTRICT Trigger for Parent Table
• 9-22 UPDATE and DELETE SET NULL Triggers for Parent Table
• 9-23 DELETE Cascade Trigger for Parent Table
• 9-24 UPDATE Cascade Trigger for Parent Table
• 9-25 Trigger for Complex Check Constraints
• 9-26 Trigger for Enforcing Security
• 9-27 Trigger That Derives New Column Values for Table
• 10-1 Serially Reusable Package Specification
• 10-2 Serially Reusable Package Specification and Package Body
• 10-3 Open Cursors in Serially Reusable Packages at Call Boundaries
• 10-4 Simple Package Specification Without Body
• 10-5 Matching Package Specification and Body
• 10-6 Creating emp_admin Package
• 10-7 DBMS_OUTPUT.PUT_LINE Procedure
• 10-8 Separating Cursor Specifications with Packages
• 10-9 Referencing Packaged Cursor
• 11-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
• 11-2 DBMS_WARNING Package
• 11-3 Anonymous Block with Exception Handlers
• 11-4 Avoiding the Exception that Example 11-3 Handles
• 11-5 Managing Multiple Errors with a Single Exception Handler
• 11-6 Scope of Exceptions
• 11-7 PRAGMA EXCEPTION_INIT
• 11-8 RAISE_APPLICATION_ERROR Procedure
• 11-9 Raising a User-Defined Exception
• 11-10 Raising a Predefined Exception
• 11-11 Scope of an Exception
• 11-12 Reraising an Exception
• 11-13 Raising an Exception in a Declaration
• 11-14 Displaying SQLCODE and SQLERRM
• 11-15 Exception Handler
• 11-16 Continuing After an Exception
• 11-17 Retrying a Transaction After an Exception
• 11-18 Identifying Exception Locations with Locator Variables
• 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 Nesting a Query to Improve Performance
• 12-6 Issuing DELETE Statements in a Loop
• 12-7 Issuing INSERT Statements in a Loop
• 12-8 FORALL Statement for Part of Collection
• 12-9 FORALL Statement for Nonconsecutive Index Values
• 12-10 Rollbacks with FORALL Statement
• 12-11 FORALL Statement and SQL%BULK_EXCEPTIONS
• 12-12 FORALL Statement and SQL%BULK_ROWCOUNT
• 12-13 Counting Rows Affected by FORALL with SQL%BULK_ROWCOUNT
• 12-14 Retrieving Query Results with BULK COLLECT
• 12-15 Limiting Query Results with Pseudocolumn ROWNUM
• 12-16 Bulk-Fetching from a Cursor Into One or More Collections
• 12-17 Bulk-Fetching from a Cursor Into a Collection of Records
• 12-18 Controlling Number of BULK COLLECT Rows with LIMIT
• 12-19 BULK COLLECT with RETURNING INTO Clause
• 12-20 FORALL with BULK COLLECT
• 12-21 SELECT BULK COLLECT INTO Statement with Unexpected Results
• 12-22 Cursor Workaround for Example 12-21
• 12-23 Second Collection Workaround for Example 12-21
• 12-24 Associating a Cursor with a Dynamic SELECT Statement
• 12-25 NOCOPY with Parameters
• 12-26 Assigning the Result of a Table Function
• 12-27 Pipelined Table Function for Transformation
• 12-28 Function with Two Cursor Variable Parameters
• 12-29 Pipelined Table Function as Aggregate Function
• 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