Collections in PL/SQL
There are three types of collections in PL/SQL: associative arrays (formerly known as index-by tables or PL/SQL tables), nested tables, and VARRAYs.
- Associative arrays
Single-dimension, unbounded collections of homogeneous elements available only in PL/SQL, not in the Oracle database. Associative arrays are initially sparse; they have nonconsecutive subscripts. There are two types of associative arrays: INDEX BY BINARY_INTEGER, which allows you to associate a value with a BINARY_INTEGER, and INDEX BY VARCHAR2, which allows you to associate a value with a text string.
- Nested tables
Single-dimension, unbounded collections of homogeneous elements available in both PL/SQL and the Oracle database as a column of a table. Nested tables initially are dense (they have consecutive subscripts), but they can become sparse through deletions.
Variable-size arrays. Single-dimension, bounded collections of homogeneous elements available in both PL/SQL and the Oracle 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:
Usable in SQL?
Usable as a column datatype in a table?
Yes; data stored “out of line” (in a separate table)
Yes; data typically stored “inline” (in the same table)
Empty (cannot be NULL); elements are undefined ...