O'Reilly logo

Oracle in a Nutshell by David C. Kreines, Rick Greenwald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required