Collections

There are three types of collections: index-by tables (formerly known as PL/SQL tables), nested tables, and VARRAYs.

Index-by table

Single-dimension, unbounded collections of homogeneous elements available only in PL/SQL, not in the database. Index-by tables are initially sparse; they have nonconsecutive subscripts.

Nested table

Single-dimension, unbounded collections of homogeneous elements available in both PL/SQL and the database as columns or tables. Nested tables are initially dense (they have consecutive subscripts), but can become sparse through deletions.

VARRAYs

Variable-size arrays are single-dimension, bounded collections of homogeneous elements available in both PL/SQL and the database. VARRAYs are never sparse. Unlike nested tables, their element order is preserved when you store and retrieve them from the database.

The following table compares these similar collection types.

Collection Type

Characteristic

Index-by Table

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

In PL/SQL, elements ...

Get Oracle PL/SQL Language Pocket Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.