Oracle PL/SQL for DBAs

Types of Collections

Oracle supports three different types of collections. While these different types have much in common, they also each have their own particular characteristics.

Associative arrays

These are single-dimensional, unbounded, sparse collections of homogeneous elements that are available only in PL/SQL. They were called PL/SQL tables in PL/SQL 2 and index-by tables in Oracle8 Database and Oracle8i Database (because, when you declare such a collection, you explicitly state that they are “indexed by” the row number). In Oracle9i Database, the name was changed to associative arrays. The motivation for the name change was that starting with that release, the INDEX BY syntax could be used to “associate” or index contents by VARCHAR2 or PLS_INTEGER.

Nested tables

These are also single-dimensional, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables can be defined in both PL/SQL and the database (for example, as a column in a table). Nested tables are multisets, which means that there is no inherent order to the elements in a nested table.

VARRAYs

Like the other two collection types, variable-sized arrays (VARRAYs) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse. When you define a type of VARRAY, you must also specify the maximum number of elements it can contain. Like nested tables, they can be used in PL/SQL and in the database. ...