Oracle Collections Demystified: Nested Tables, VARRAYs, and Associative Arrays

Three Collection Types, Three Use Cases

PL/SQL provides three collection types. Choosing the right one affects both code clarity and performance.

Associative Arrays (INDEX BY Tables)

The most commonly used type. They exist only in PL/SQL memory, cannot be stored in columns, and are indexed by PLS_INTEGER or VARCHAR2. No initialization needed:

DECLARE
    TYPE t_salary_map IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
    l_salaries t_salary_map;
BEGIN
    l_salaries('SMITH') := 50000;
    l_salaries('JONES') := 65000;
    IF l_salaries.EXISTS('SMITH') THEN
        DBMS_OUTPUT.PUT_LINE('Smith: ' || l_salaries('SMITH'));
    END IF;
END;

Use associative arrays for lookup tables, caching, and any key-value pair scenario.

Nested Tables

Can be used in SQL (unlike associative arrays) and stored in columns. Must be initialized before use:

DECLARE
    TYPE t_num_list IS TABLE OF NUMBER;
    l_ids t_num_list := t_num_list(100, 200, 300);
BEGIN
    FOR rec IN (
        SELECT employee_id, first_name FROM employees
         WHERE department_id IN (SELECT column_value FROM TABLE(l_ids))
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.first_name);
    END LOOP;
END;

Use nested tables when passing collections to SQL using the TABLE operator, with BULK COLLECT, or for set operations like MULTISET UNION.

VARRAYs

Variable-Size Arrays have a fixed maximum size, maintain order, and are always dense. They are rarely used because nested tables are more flexible. Use VARRAYs when you have a genuinely fixed maximum size like days of the week.

Choosing the Right Collection

Need key-value lookup in PL/SQL only? Associative arrays. Need to use the collection in SQL or BULK COLLECT? Nested tables. Need a fixed-size ordered list in a table column? VARRAYs. When in doubt, start with nested tables for the best balance of flexibility and SQL interoperability.