Oracle PL/SQL Programming, 5th Edition

Working with Collections

You now know about the different types of collections and the collection methods. You have seen some examples of working with associative arrays, nested tables and VARRAYs. Now it is time to dive into the details of manipulating collections in your programs. Topics in this section include:

• Exception handling with collections

• Declaring collection types

• Declaring and initializing collection variables

• Assigning values to collections

• Using collections of complex datatypes, such as collections of other collections

• Working with sequential and nonsequential associative arrays

• The power of string-indexed collections

• Working with PL/SQL collections inside SQL statements

Declaring Collection Types

Before you can work with a collection, you must declare it, and that declaration must be based on a collection type. So the first thing you must learn to do is define a collection type.

There are two ways to create user-defined collection types :

• You can declare the collection type within a PL/SQL program using the TYPE statement. This collection type will then be available for use within the block in which the TYPE is defined. If the TYPE is defined in a package specification, then it is available to any program whose schema has EXECUTE authority on the package.

• You can define a nested table type or VARRAY type as a schema-level object within the Oracle database by using the CREATE TYPE command. This TYPE can then be used as the datatype for columns in database tables and attributes ...