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

Three Collection Types, Three Use Cases

PL/SQL provides three collection types, and choosing the right one for your situation affects both code clarity and performance. Each type has distinct characteristics that make it suited to different scenarios.

Associative Arrays (INDEX BY Tables)

Associative arrays are the most commonly used collection type. They exist only in PL/SQL memory, cannot be stored in database columns, and are indexed by PLS_INTEGER or VARCHAR2. They do not need initialization and grow automatically:

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;
    l_salaries('BROWN') := 72000;

    -- Check if a key exists
    IF l_salaries.EXISTS('SMITH') THEN
        DBMS_OUTPUT.PUT_LINE('Smith earns: ' || l_salaries('SMITH'));
    END IF;

    -- Iterate
    DECLARE
        l_key VARCHAR2(100) := l_salaries.FIRST;
    BEGIN
        WHILE l_key IS NOT NULL LOOP
            DBMS_OUTPUT.PUT_LINE(l_key || ': ' || l_salaries(l_key));
            l_key := l_salaries.NEXT(l_key);
        END LOOP;
    END;
END;

Use associative arrays for lookup tables, caching query results in memory, and any scenario where you need key-value pair access. Their VARCHAR2 index capability makes them ideal for mapping codes to descriptions or building in-memory dictionaries.

Nested Tables

Nested tables can be used in SQL (unlike associative arrays) and can be stored in database columns. They are dense initially but can become sparse if you delete elements. They must be initialized before use:

DECLARE
    TYPE t_num_list IS TABLE OF NUMBER;
    l_ids t_num_list := t_num_list();  -- Must initialize
BEGIN
    l_ids.EXTEND(3);
    l_ids(1) := 100;
    l_ids(2) := 200;
    l_ids(3) := 300;

    -- Use in SQL with TABLE() operator
    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 you need to pass collections to SQL statements using the TABLE operator, when using BULK COLLECT, or when you need set operations like MULTISET UNION and MULTISET INTERSECT.

VARRAYs

VARRAYs (Variable-Size Arrays) have a fixed maximum size declared at creation time. They maintain element order and are always dense (no gaps). They are rarely used in practice because nested tables are more flexible:

DECLARE
    TYPE t_color_list IS VARRAY(10) OF VARCHAR2(50);
    l_colors t_color_list := t_color_list('Red', 'Green', 'Blue');
BEGIN
    l_colors.EXTEND;
    l_colors(4) := 'Yellow';
END;

Use VARRAYs when you have a genuinely fixed maximum size, such as the days of the week or the months of the year, and you want to enforce that limit at the type level.

Choosing the Right Collection

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