Understanding PL/SQL Collections: Index‑by Tables, Nested Tables, and VARRAYs

Overview of PL/SQL Collections

Oracle PL/SQL does not have a native array type; instead it provides three collection types that serve similar purposes: index‑by tables (also called associative arrays), nested tables , and VARRAYs . Index‑by tables are sparse and cannot be stored in the database, while nested tables and VARRAYs are dense and can be persisted.

1. Index‑by Tables (Associative Arrays)

Definition syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;

Key characteristics:

Must be initialized before use; otherwise ORA-01403: no data found occurs.

No upper bound on the number of elements.

Indexes can be non‑sequential and may be negative.

Example:

TYPE TYPE1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

v1 TYPE1;

v1(-1) := '-1';
v1(0)  := '0';
v1(1)  := '1';

DBMS_OUTPUT.PUT_LINE(v1(-1)); -- valid access
DBMS_OUTPUT.PUT_LINE(v1(2));  -- raises ORA-01403 (invalid index)

2. Nested Tables

Definition syntax: TYPE type_name IS TABLE OF element_type [NOT NULL]; Important points:

Must be initialized; otherwise ORA-06531: Reference to uninitialized collection is raised.

Initialization can be empty or with predefined elements.

Indexes start at 1 and must not exceed the total number of elements.

Examples:

TYPE TYPE2 IS TABLE OF VARCHAR2(10);

v1 TYPE2 := TYPE2();               -- empty collection
v2 TYPE2 := TYPE2('1','2','3','4','5'); -- collection with 5 elements

v1.EXTEND;                         -- add one element (now size = 1)
v1(1) := '1';                      -- valid access
v1(2) := '2';                      -- must EXTEND again before this access

v2(5) := 'Hello';                 -- valid access
DBMS_OUTPUT.PUT_LINE(v2(6));      -- raises ORA-06532 (subscript out of limit)

3. VARRAYs

Definition syntax (size‑limited):

TYPE type_name IS VARRAY(max_size) OF element_type [NOT NULL];

Characteristics:

The maximum number of elements is fixed at declaration (e.g., 5 in the example).

Behaviour is similar to nested tables, but the LIMIT method applies only to VARRAYs.

4. Built‑in Collection Methods

Oracle provides a set of methods that can be invoked on any collection using the syntax collection.method. The most commonly used methods include:

COUNT : returns the number of elements.

DELETE : removes all elements or a specific element/range (not valid for VARRAYs).

EXIST(x) : returns TRUE if element x is initialized.

EXTEND , EXTEND(n) , EXTEND(x,n) : adds elements to the end (invalid for index‑by tables).

FIRST / LAST : return the first or last index (FIRST is always 1 for VARRAYs).

LIMIT : returns the maximum size of a VARRAY (not applicable to nested tables or index‑by tables).

NEXT(x) / PRIOR(x) : return the index after or before x, or NULL if none.

TRIM , TRIM(n) : remove elements from the end (invalid for index‑by tables).

These methods enable developers to query, modify, and manage collection contents efficiently within PL/SQL programs.