Oracle PL/SQL Collections: Nested Tables, VARRAYs, and Associative Arrays

The three collection types

Associative Arrays (INDEX BY tables): key-value maps. Keys can be integers or strings. Only exist in PL/SQL memory — not storable in database tables. Best for lookup caches and temporary working sets.

Nested Tables: ordered collections storable in database table columns. Support set operations (MULTISET UNION, EXCEPT). Can be sparse (elements removed, indices gap).

VARRAYs: fixed-maximum-size arrays, always dense, storable in database columns. Best for attributes with a known maximum count (e.g. “up to 5 phone numbers per contact”).

Associative arrays as lookup caches

DECLARE
  TYPE t_lookup IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);
  l_currency t_lookup;
BEGIN
  -- Load once
  FOR r IN (SELECT code, name FROM currencies) LOOP
    l_currency(r.code) := r.name;
  END LOOP;
  
  -- Access many times, no repeated DB calls
  DBMS_OUTPUT.PUT_LINE(l_currency('USD')); -- US Dollar
END;

Nested table operations

DECLARE
  TYPE t_nums IS TABLE OF NUMBER;
  l_a t_nums := t_nums(1,2,3,4,5);
  l_b t_nums := t_nums(3,4,5,6,7);
  l_result t_nums;
BEGIN
  l_result := l_a MULTISET INTERSECT l_b; -- {3,4,5}
  l_result := l_a MULTISET UNION l_b;     -- {1,2,3,4,5,3,4,5,6,7}
  l_result := l_a MULTISET EXCEPT l_b;    -- {1,2}
END;

Bulk collect into collections

DECLARE
  TYPE t_emp_ids IS TABLE OF NUMBER;
  l_ids t_emp_ids;
BEGIN
  SELECT employee_id BULK COLLECT INTO l_ids
  FROM employees WHERE department_id = 60;
  DBMS_OUTPUT.PUT_LINE('Count: ' || l_ids.COUNT);
END;

TABLE() function: using collections in SQL

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  l_ids t_ids := t_ids(101, 102, 103);
BEGIN
  FOR r IN (SELECT * FROM employees WHERE employee_id IN (SELECT * FROM TABLE(l_ids)))
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.last_name);
  END LOOP;
END;

Common collection methods

• l_coll.COUNT — number of elements
• l_coll.FIRST, l_coll.LAST — first/last index
• l_coll.EXISTS(i) — check if index exists (safe before access)
• l_coll.DELETE(i) — remove element
• l_coll.TRIM(n) — remove n elements from end
• l_coll.EXTEND(n) — add n null elements to end