List of Examples

5-1 Declaring a Nested Table in SQL

5-2 Creating a Table with a Varray Column

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 Specifying Collection Element Types with %TYPE and %ROWTYPE

5-7 VARRAY of Records

5-8 NOT NULL Constraint on Collection Elements

5-9 Constructor for a Nested Table

5-10 Constructor for a Varray

5-11 Collection Constructor Including Null Elements

5-12 Combining Collection Declaration and Constructor

5-13 Empty Varray Constructor

5-14 Nested Table Constructor Within a SQL Statement

5-15 Varray Constructor Within a SQL Statement

5-16 Referencing a Nested Table Element By Subscript

5-17 Passing a Nested Table Element as a Parameter

5-18 Datatype Compatibility for Collection Assignment

5-19 Assigning a Null Value to a Nested Table

5-20 Possible Exceptions for Collection Assignments

5-21 Assigning Nested Tables with Set Operators

5-22 Checking if a Collection Is Null

5-23 Comparing Two Collections

5-24 Comparing Nested Tables with Set Operators

5-25 Creating a SQL Type Corresponding to a PL/SQL Nested Table

5-26 Inserting a Nested Table into a Database Table

5-27 Using PL/SQL Nested Tables with INSERT, UPDATE, DELETE, and SELECT Statements

5-28 Updating a Nested Table within a Database Table

5-29 Performing INSERT, UPDATE, and DELETE Operations on PL/SQL Nested Tables

5-30 Multilevel VARRAY

5-31 Multilevel Nested Table

5-32 Multilevel Associative Array

5-33 Multilevel Collections and Bulk SQL

5-34 Inserting a PL/SQL Record Using %ROWTYPE

5-35 Updating a Row Using a Record

5-36 Using the RETURNING Clause with a Record

6-1 Passing Parameters to a Cursor FOR Loop

6-2 Passing Parameters to Explicit Cursors

6-3 Cursor Variable Returning %ROWTYPE

6-4 Cursor Variable Returning %TYPE

6-5 Cursor Variable Returning Record Type

6-6 Stored Procedure to Open a Ref Cursor

6-7 Stored Procedure to Open Ref Cursors with Different Queries

6-8 Cursor Variable with Different Return Types

6-9 Fetching from a Cursor Variable into a Record

6-10 Fetching from a Cursor Variable into Collections

7-1 Some Examples of Dynamic SQL

7-2 Dynamic SQL Procedure that Accepts Table Name and WHERE Clause

7-3 Dynamic SQL Fetching into a Record

7-4 Dynamic SQL for Object Types and Collections

7-5 Dynamic SQL with BULK COLLECT INTO Clause

7-6 Dynamic SQL with RETURNING BULK COLLECT INTO Clause

7-7 Dynamic SQL Inside FORALL Statement

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 Procedure with Default Parameter Values

8-7 Aliasing from Passing Global Variable with NOCOPY Hint

8-8 Aliasing Passing Same Parameter Multiple Times

8-9 Aliasing from Assigning Cursor Variables to Same Work Area

11-1 Issuing DELETE Statements in a Loop

11-2 Issuing INSERT Statements in a Loop

11-3 Using FORALL with Part of a Collection

11-4 Using FORALL with Non-Consecutive Index Values

11-5 Bulk Operation That Continues Despite Exceptions

11-6 Bulk-Fetching from a Cursor Into One or More Collections

11-7 Bulk-Fetching from a Cursor Into a Collection of Records

11-8 Compiling a PL/SQL Procedure for Native Execution

11-9 Example: Querying a Table Function

11-10 Example: Assigning the Result of a Table Function

11-11 Example: Using Multiple REF CURSOR Input Variables

11-12 Example: Explicitly Opening a REF CURSOR for a Query

11-13 Example: Using a Pipelined Table Function as an Aggregate Function