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
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 ...