Collections (Arrays, VARRAYs, Nested Tables)

PL/SQL collections are ordered groups of elements of the same type — PL/SQL's version of arrays and lists. Three collection types exist, each with distinct rules about size, sparseness, and storage.

The Three Collection Types

Associative Arrays (Index-By Tables)

The most flexible and commonly used collection. Indexed by PLS_INTEGER or VARCHAR2:

DECLARE
    -- Integer-indexed: like a zero-based or arbitrary-index array
    TYPE t_salary_list IS TABLE OF employees.salary%TYPE
        INDEX BY PLS_INTEGER;

    -- String-indexed: like a hash map
    TYPE t_dept_map IS TABLE OF VARCHAR2(100)
        INDEX BY VARCHAR2(30);

    v_salaries  t_salary_list;
    v_depts     t_dept_map;
    v_idx       PLS_INTEGER;
BEGIN
    -- Populate integer-indexed collection
    v_salaries(1) := 24000;
    v_salaries(2) := 17000;
    v_salaries(3) := 13500;
    v_salaries(100) := 99999;  -- sparse — gaps are fine

    -- Populate string-indexed collection
    v_depts('IT')      := 'Information Technology';
    v_depts('HR')      := 'Human Resources';
    v_depts('FINANCE') := 'Finance';

    -- Iterate integer-indexed
    v_idx := v_salaries.FIRST;
    WHILE v_idx IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('Index ' || v_idx || ': $' || v_salaries(v_idx));
        v_idx := v_salaries.NEXT(v_idx);
    END LOOP;

    -- Lookup string-indexed
    DBMS_OUTPUT.PUT_LINE('IT dept: ' || v_depts('IT'));
END;
/

VARRAY (Variable-Size Array)

Fixed maximum size, always dense, sequential indexing starting at 1:

DECLARE
    -- Maximum 5 elements
    TYPE t_skills IS VARRAY(5) OF VARCHAR2(50);

    v_emp_skills  t_skills := t_skills('SQL', 'PL/SQL', 'Java');
BEGIN
    DBMS_OUTPUT.PUT_LINE('Skills count: ' || v_emp_skills.COUNT);
    DBMS_OUTPUT.PUT_LINE('Max capacity: ' || v_emp_skills.LIMIT);

    -- Extend before adding (VARRAY needs EXTEND)
    v_emp_skills.EXTEND;
    v_emp_skills(4) := 'Python';

    -- Iterate
    FOR i IN 1..v_emp_skills.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(i || '. ' || v_emp_skills(i));
    END LOOP;
END;
/

Nested Tables

Like a VARRAY but unlimited size and can become sparse after DELETE:

DECLARE
    TYPE t_phone_list IS TABLE OF VARCHAR2(20);

    v_phones  t_phone_list := t_phone_list(
        '515.123.4567', '515.123.4568', '515.123.4569'
    );
BEGIN
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_phones.COUNT);

    v_phones.EXTEND(2);
    v_phones(4) := '515.999.0001';
    v_phones(5) := '515.999.0002';

    -- DELETE creates a sparse collection
    v_phones.DELETE(3);  -- remove element at index 3

    -- Must use FIRST/NEXT because it may be sparse
    DECLARE v_i PLS_INTEGER := v_phones.FIRST;
    BEGIN
        WHILE v_i IS NOT NULL LOOP
            IF v_phones.EXISTS(v_i) THEN
                DBMS_OUTPUT.PUT_LINE(v_i || ': ' || v_phones(v_i));
            END IF;
            v_i := v_phones.NEXT(v_i);
        END LOOP;
    END;
END;
/

Collection Methods

DECLARE
    TYPE t_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_l  t_list;
BEGIN
    v_l(1) := 10; v_l(2) := 20; v_l(5) := 50;

    DBMS_OUTPUT.PUT_LINE('COUNT:  ' || v_l.COUNT);    -- 3 (populated elements)
    DBMS_OUTPUT.PUT_LINE('FIRST:  ' || v_l.FIRST);    -- 1
    DBMS_OUTPUT.PUT_LINE('LAST:   ' || v_l.LAST);     -- 5
    DBMS_OUTPUT.PUT_LINE('NEXT(2): ' || v_l.NEXT(2)); -- 5 (skips gap at 3,4)
    DBMS_OUTPUT.PUT_LINE('PRIOR(5): ' || v_l.PRIOR(5)); -- 2
    DBMS_OUTPUT.PUT_LINE('EXISTS(3): ' || CASE WHEN v_l.EXISTS(3) THEN 'Y' ELSE 'N' END); -- N

    v_l.DELETE(2);   -- delete element at index 2
    DBMS_OUTPUT.PUT_LINE('After DELETE(2), COUNT: ' || v_l.COUNT);  -- 2

    v_l.DELETE;      -- delete ALL elements
    DBMS_OUTPUT.PUT_LINE('After DELETE, COUNT: ' || v_l.COUNT);  -- 0
END;
/

Method Reference

Multilevel Collections

Collections of collections:

DECLARE
    TYPE t_row   IS TABLE OF NUMBER  INDEX BY PLS_INTEGER;
    TYPE t_matrix IS TABLE OF t_row  INDEX BY PLS_INTEGER;

    v_matrix  t_matrix;
BEGIN
    -- 3x3 matrix
    FOR r IN 1..3 LOOP
        FOR c IN 1..3 LOOP
            v_matrix(r)(c) := r * 10 + c;
        END LOOP;
    END LOOP;

    -- Print
    FOR r IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE(
            v_matrix(r)(1) || '  ' ||
            v_matrix(r)(2) || '  ' ||
            v_matrix(r)(3)
        );
    END LOOP;
END;
/

Summary

• Associative arrays — flexible, PL/SQL-only, integer or string index.
• VARRAYs — fixed max size, always dense, storable in table columns.
• Nested tables — unlimited size, can be sparse after DELETE, storable in table columns.
• Methods (COUNT, FIRST, LAST, NEXT, PRIOR, EXISTS, EXTEND, DELETE, TRIM, LIMIT) work on all or specific types.
• Use FIRST / NEXT for safe iteration over potentially sparse collections.
• Multilevel (nested) collections are supported — index with multiple subscripts.