Oracle in a Nutshell

Collections

A collection is a composite data structure that acts like a list or a single-dimensional array (traditional arrays are not supported in PL/SQL).

There are three different types of collections in PL/SQL: associative arrays (called index-by tables in Oracle8 and Oracle8i and PL/SQL tables before that), nested tables, and VARRAYs (variable arrays). The following sections briefly describe the declarations for these different types, but for information about using and comparing these collection types, see Section 9.4.11 under Section 9.4 later in this chapter. Table 9-2 compares the three collection types.

Collections are implemented as TYPEs. Like any programmer-defined type, you must first define the type; then you can declare instances of that type. The TYPE definition can be stored in the database or declared in the PL/SQL program. Each instance of the TYPE is a collection.

Table 9-2. Comparison of collection types

Characteristic	Associative array	Nested table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as a column datatype in a table?	No	Yes; data stored “out of line” (in a separate table)	Yes; data typically stored “in line” (in the same table)
Uninitialized state	Empty (cannot be NULL); elements are undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment ...