Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 38 ยท PL/SQL Basics

PL/SQL Collections and Bulk Processing

Store lists in PL/SQL with associative arrays, nested tables and VARRAYs, use collection methods, and speed up DML with BULK COLLECT and FORALL.

A single variable holds one value, but real programs handle lists: the ids to process, the lines of an order, a lookup table of exchange rates. PL/SQL has three collection types for that. Combined with BULK COLLECT and FORALL, they are also the main tool for making database code fast, because they move many rows between SQL and PL/SQL in a single step.

The collection code is PL/SQL and read-only. Runnable stand-ins at the end use SQLite's JSON functions to model a list, and set-based SQL to show why bulk operations beat loops. Prerequisites: control flow and records.

Three kinds of collection

Type Index Bounded? Dense? Can be a table column?
Associative array (INDEX BY) Number or string, any value No Can have gaps No, PL/SQL only
Nested table Integer from 1 No Starts dense, DELETE can make gaps Yes (SQL type)
VARRAY Integer from 1 Fixed maximum size Always dense Yes (SQL type)

Use an associative array for in-memory lookups (the most common choice), a nested table when you need set operations or want to pass a list to SQL, and a VARRAY when the maximum size is part of the design.

Associative arrays

PLSQL
DECLARE
  TYPE t_rates IS TABLE OF NUMBER INDEX BY VARCHAR2(3);
  v_rates t_rates;
BEGIN
  v_rates('EUR') := 1.00;
  v_rates('USD') := 1.08;
  v_rates('GBP') := 0.85;

  DBMS_OUTPUT.PUT_LINE('USD rate: ' || v_rates('USD'));
  DBMS_OUTPUT.PUT_LINE('Currencies: ' || v_rates.COUNT);
END;
/

The output is USD rate: 1.08 and Currencies: 3. Elements spring into existence when assigned; there is nothing to initialize or extend. Reading a key that does not exist raises NO_DATA_FOUND, so check with v_rates.EXISTS('JPY') first.

To walk the elements in key order, use FIRST and NEXT:

PLSQL
DECLARE
  k VARCHAR2(3);
BEGIN
  k := v_rates.FIRST;
  WHILE k IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(k || ' = ' || v_rates(k));
    k := v_rates.NEXT(k);
  END LOOP;
END;

It prints EUR = 1, GBP = .85, USD = 1.08 (sorted by key; note how Oracle shows numbers without a leading zero).

Nested tables and VARRAYs

These two must be initialized with a constructor before use:

PLSQL
DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(30);          -- nested table
  TYPE t_top3  IS VARRAY(3) OF NUMBER;            -- at most 3 elements
  v_names t_names := t_names('Ada', 'Grace');
  v_top   t_top3  := t_top3(10, 20);
BEGIN
  v_names.EXTEND;                  -- add an empty slot
  v_names(3) := 'Alan';
  v_names.EXTEND(2);               -- two more slots
  v_names(4) := 'Edsger';
  FOR i IN 1 .. v_names.COUNT LOOP
    IF v_names(i) IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE(i || ': ' || v_names(i));
    END IF;
  END LOOP;
END;
/

This prints 1: Ada, 2: Grace, 3: Alan, 4: Edsger (slot 5 is empty and skipped). Forgetting the constructor leads to ORA-06531: Reference to uninitialized collection; writing beyond a nested table's size without EXTEND leads to ORA-06533: Subscript beyond count.

Nested tables and VARRAYs defined at schema level with CREATE TYPE t_phone_list AS TABLE OF VARCHAR2(20); can be table columns, passed between PL/SQL and SQL, and queried with the TABLE() operator:

SQL
SELECT column_value FROM TABLE(t_phone_list('555-0100', '555-0101'));

Collection methods

Method Result
COUNT Number of elements
FIRST, LAST Lowest and highest index
NEXT(i), PRIOR(i) Following and preceding index
EXISTS(i) TRUE if element i exists
EXTEND[(n)] Add one or n empty slots (nested table, VARRAY)
TRIM[(n)] Remove from the end
DELETE[(i)] Remove element i, or all when called without a parameter
LIMIT Maximum size (VARRAY only)

Use FIRST ... LAST loops only for dense collections; with gaps use the NEXT pattern. In 21c and later, FOR i IN INDICES OF v_names LOOP and VALUES OF iterate sparse collections directly.

BULK COLLECT

Each time PL/SQL runs a SQL statement, control passes between the PL/SQL engine and the SQL engine, a context switch. In a loop that is repeated for every row, and it adds up. BULK COLLECT fetches all the rows in one switch:

PLSQL
DECLARE
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  v_emps t_emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps
  FROM employees
  WHERE department_id = 10;

  DBMS_OUTPUT.PUT_LINE('Fetched ' || v_emps.COUNT || ' rows');
  FOR i IN 1 .. v_emps.COUNT LOOP
    NULL;   -- work with v_emps(i).last_name ...
  END LOOP;
END;
/

The collection is initialized automatically, and is empty (not NO_DATA_FOUND) when no rows match. The whole result sits in memory, so for millions of rows fetch in chunks using a cursor with LIMIT:

PLSQL
DECLARE
  CURSOR c IS SELECT employee_id, salary FROM employees;
  TYPE t_tab IS TABLE OF c%ROWTYPE;
  v_rows t_tab;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rows LIMIT 1000;
    EXIT WHEN v_rows.COUNT = 0;
    -- process up to 1000 rows
  END LOOP;
  CLOSE c;
END;
/

Note the exit test uses v_rows.COUNT = 0, not c%NOTFOUND: with BULK COLLECT, the last batch can be smaller than the limit and sets NOTFOUND while still holding rows.

FORALL

FORALL is the bulk version of DML. It sends the whole collection to SQL in one switch:

PLSQL
DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_ids := t_ids(101, 102, 103, 104);
BEGIN
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE employees SET salary = salary * 1.03
    WHERE  employee_id = v_ids(i);

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');   -- total across the batch
END;
/

FORALL is not a loop. It contains exactly one DML statement, and Oracle executes it for each index. It is typically ten to a hundred times faster than a FOR loop with the same statement inside. SAVE EXCEPTIONS lets the batch continue past failing rows, collecting errors in SQL%BULK_EXCEPTIONS; SQL%BULK_ROWCOUNT(i) returns the rows touched by the i-th execution.

When no collection is needed

If a loop does nothing except run the same UPDATE or INSERT per row, one SQL statement is faster still, with no PL/SQL at all. Reach for collections when each element needs logic that SQL cannot express.

flowchart LR
  A["Row-by-row loop<br/>N context switches"] -->|"slow"| Z["Result"]
  B["BULK COLLECT + FORALL<br/>2 context switches"] -->|"faster"| Z
  C["One SQL statement<br/>0 extra switches"] -->|"fastest"| Z

Prefer the lowest path that your logic allows.

Stand-ins you can run

A collection is a list of values. SQLite's json_each turns a JSON array into rows, which is the SQL counterpart of looping over v_names:

SQL Stand-in: iterating a list (like FOR i IN 1..v_names.COUNT)

Press Run (or Ctrl + Enter) to see the result here.

An associative array keyed by a string is a lookup table, and a join is the set-based way to use it:

SQL Stand-in: an associative array as a lookup join

Press Run (or Ctrl + Enter) to see the result here.

And the FORALL update becomes a single set-based statement that touches all listed ids at once:

SQL Stand-in for FORALL: one UPDATE for many ids

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which collection type must be initialized with a constructor before use?

    Nested tables and VARRAYs need a constructor such as t_names('Ada') (or an empty t_names()). Associative arrays need no initialization.
  2. 2. What does BULK COLLECT INTO do when the query returns no rows?

    Unlike a scalar SELECT INTO, BULK COLLECT returns an empty collection when nothing matches.
  3. 3. What is FORALL?

    FORALL is not a loop: it holds a single DML statement that Oracle executes for each index in one operation.
  4. 4. Why should a FETCH ... BULK COLLECT ... LIMIT n loop use EXIT WHEN v.COUNT = 0?

    Exiting on %NOTFOUND right after the fetch would skip the final partial batch.
  5. 5. Reading v_rates('JPY') from an associative array with no such key raises:

    Use EXISTS to test for a key before reading it.

Summary

  • Associative arrays are in-memory maps; nested tables and VARRAYs are SQL-compatible lists that need constructors and EXTEND.
  • Collection methods such as COUNT, FIRST, NEXT, EXISTS, EXTEND and DELETE navigate and change them.
  • BULK COLLECT fetches many rows at once; use LIMIT for large results.
  • FORALL runs one DML statement for a whole collection and is far faster than a row-by-row loop.
  • If plain SQL can do the job set-wise, it is faster than any collection code.

Next: Implicit and Explicit Cursors in PL/SQL