# Deep Dive — FORALL

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

Used in: [Chapter 2 — Bulk](../course-plan.md) · Appendix entry: [FORALL](../appendix.md#forall) · Related: [SAVE EXCEPTIONS](save-exceptions.md), [BULK COLLECT](bulk-collect.md), [COLLECTION METHODS](collection-methods.md), [APPEND HINT](append-hint.md), [EXECUTE IMMEDIATE](execute-immediate.md)

---

## 1. What It Is and When

`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](bulk-collect.md).

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](../course-plan.md)).

## 2. Syntax

```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.

## 3. Examples on the Course Schema

### 3a. UPDATE with record fields (11g+)

```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;
```

### 3b. INSERT a whole record per element

```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
```

### 3c. DELETE with RETURNING — one collection in, one out

```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;
```

### 3d. Sparse collections — INDICES OF

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;
```

### 3e. A subset in a chosen order — VALUES OF

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;
```

### 3f. Dynamic SQL inside FORALL

```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](dbms_assert.md)).

### 3g. Per-iteration row counts

```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;
```

## 4. What Breaks at Scale

- **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](save-exceptions.md).
- **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](limit.md) 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.

## 5. Pitfalls

1. More than one statement in the body — compile error. Two `FORALL`s 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.

## 6. Related

- [SAVE EXCEPTIONS](save-exceptions.md) — surviving bad rows.
- [BULK COLLECT](bulk-collect.md) / [LIMIT](limit.md) — where the collection comes from.
- [COLLECTION METHODS](collection-methods.md) — `DELETE(i)`, `EXISTS`, `FIRST/NEXT` for sparse work.
- [APPEND HINT](append-hint.md) — `APPEND_VALUES`.
- [EXECUTE IMMEDIATE](execute-immediate.md) — the dynamic form.