Deep Dive — FORALL

Not a loop. One DML statement, handed to the SQL engine with the whole collection attached.

Used in
Chapter 2 — Bulk
Appendix entry
FORALL
Related
SAVE EXCEPTIONS BULK COLLECT COLLECTION METHODS APPEND HINT EXECUTE IMMEDIATE

FORALL sends one INSERT, UPDATE, DELETE or MERGE to the SQL engine together with a collection, and the SQL engine executes it once per element — inside a single context switch. It is the write-side counterpart of BULK COLLECT.

The keyword looks like FOR, but there is no loop body: exactly one DML statement, and the index variable may only be used as a collection subscript.

Use it every time PL/SQL has to write more than a handful of rows that it computed in memory. If the rows can be computed in SQL, use no PL/SQL at all (Chapter 1).

SQL 
FORALL <i> IN <lower> .. <upper>            [SAVE EXCEPTIONS] <dml_statement>;
FORALL <i> IN INDICES OF <collection>       [BETWEEN <lo> AND <hi>] [SAVE EXCEPTIONS] <dml_statement>;
FORALL <i> IN VALUES OF <index_collection>  [SAVE EXCEPTIONS] <dml_statement>;
  • <dml_statement>: static INSERT / UPDATE / DELETE / MERGE, or EXECUTE IMMEDIATE with USING <coll>(i).
  • IN lower..upper — the collection must be dense in that range (ORA-22160 otherwise).
  • INDICES OF — iterate the existing subscripts of a sparse collection.
  • VALUES OF — iterate the subscripts listed in another collection (a subset, in any order).
  • After it: SQL%ROWCOUNT (total rows affected), SQL%BULK_ROWCOUNT(i) (rows affected by iteration i), SQL%BULK_EXCEPTIONS (with SAVE EXCEPTIONS).
  • RETURNING ... BULK COLLECT INTO collects across all iterations.

SQL 
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    FOR i IN 1 .. l_rows.COUNT LOOP
      l_rows(i).amount := pricing_pkg.reprice(l_rows(i).order_id, l_rows(i).amount);
    END LOOP;
    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders
      SET    amount = l_rows(i).amount
      WHERE  order_id = l_rows(i).order_id;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated in this batch');
    COMMIT;
  END LOOP;
  CLOSE c;
END;

SQL 
FORALL i IN 1 .. l_rows.COUNT
  INSERT INTO orders_archive VALUES l_rows(i);       -- record insert, columns by position

For direct-path (above the high-water mark, minimal undo):

SQL 
FORALL i IN 1 .. l_rows.COUNT
  INSERT /*+ APPEND_VALUES */ INTO orders_archive VALUES l_rows(i);
COMMIT;                                                -- required before reading the table

SQL 
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_to_delete t_ids := t_ids(1001, 1002, 1003);
  l_deleted   t_ids;
BEGIN
  FORALL i IN 1 .. l_to_delete.COUNT
    DELETE FROM orders WHERE order_id = l_to_delete(i) AND status = 'CANCELLED'
    RETURNING order_id BULK COLLECT INTO l_deleted;
  -- l_deleted holds only the ids that actually existed with status CANCELLED
  DBMS_OUTPUT.PUT_LINE(l_deleted.COUNT || ' of ' || l_to_delete.COUNT || ' deleted');
END;

After filtering in memory you often have holes:

SQL 
FOR i IN 1 .. l_rows.COUNT LOOP
  IF l_rows(i).amount <= 0 THEN l_rows.DELETE(i); END IF;     -- leaves gaps
END LOOP;

FORALL i IN INDICES OF l_rows                                  -- 1..COUNT would hit ORA-22160
  UPDATE orders SET amount = l_rows(i).amount WHERE order_id = l_rows(i).order_id;

Retry only the elements that failed in a previous pass:

SQL 
DECLARE
  TYPE t_idx IS TABLE OF PLS_INTEGER;
  l_failed t_idx := t_idx();
BEGIN
  -- ... first FORALL with SAVE EXCEPTIONS filled l_failed with the failed subscripts ...
  FORALL i IN VALUES OF l_failed
    UPDATE orders SET amount = l_rows(i).amount, status = 'RETRY'
    WHERE  order_id = l_rows(i).order_id;
END;

SQL 
FORALL i IN 1 .. l_ids.COUNT
  EXECUTE IMMEDIATE
    'UPDATE ' || l_table || ' SET status = :1 WHERE order_id = :2'
    USING 'ARCHIVED', l_ids(i);

The string is parsed once; the collection elements are bound per iteration. l_table must have gone through DBMS_ASSERT (DBMS_ASSERT).

SQL 
FORALL i IN 1 .. l_ids.COUNT
  DELETE FROM orders WHERE customer_id = l_ids(i) AND status = 'CANCELLED';

FOR i IN 1 .. l_ids.COUNT LOOP
  IF SQL%BULK_ROWCOUNT(i) = 0 THEN
    DBMS_OUTPUT.PUT_LINE('customer ' || l_ids(i) || ': nothing to delete');
  END IF;
END LOOP;

  • It is still conventional DML. Same undo, same redo, same index maintenance, same triggers per row as the loop version — just without the context switches. For pure inserts, APPEND_VALUES gives direct path; for everything else the cost is in the SQL engine, and the win is "only" the switch overhead (which was most of the time).
  • Atomic per statement. Without SAVE EXCEPTIONS, one failing element rolls back the entire FORALL (all 10,000 iterations of that batch) and raises. With it, the good rows stay and you get the list of bad ones — SAVE EXCEPTIONS.
  • Batch size follows LIMIT. A 1M-element collection in one FORALL works, but it is one transaction and one long-held set of locks. Keep batches at the LIMIT size.
  • Triggers fire once per affected row and see each row individually — a row-level trigger that runs a query per row puts the per-row cost right back.

  1. More than one statement in the body — compile error. Two FORALLs if you need two statements.
  2. Using the index in an expression (l_ids(i + 1), i * 2) or as a value in the DML — not allowed; i is a subscript only. Put the value in the collection.
  3. ORA-22160: element at index [n] does not exist — sparse collection with IN 1..COUNT. Use INDICES OF.
  4. SQL%ROWCOUNT is the total; SQL%BULK_ROWCOUNT(i) is per iteration. %BULK_ROWCOUNT is itself a collection — do not loop 1..COUNT on it after INDICES OF; use the same subscripts.
  5. Expecting ORA-24381 without SAVE EXCEPTIONS — you get the first real error instead, and the batch is rolled back.
  6. Records with %ROWTYPE and INSERT ... VALUES rec — positional; a column added to the table in the middle breaks the insert. List columns for anything long-lived.
  7. FORALL over a collection that is empty — silently does nothing (no error, SQL%ROWCOUNT = 0). Fine, but guard with IF coll.COUNT > 0 if you rely on %ROWCOUNT afterwards.