# Deep Dive — BULK COLLECT

> One context switch brings back a whole batch of rows, not one. That single change is usually the biggest speed-up a PL/SQL loop ever gets.

Used in: [Chapter 2 — Bulk](../course-plan.md) · Appendix entry: [BULK COLLECT](../appendix.md#bulk-collect) · Related: [LIMIT](limit.md), [FORALL](forall.md), [CURSOR FOR LOOP](cursor-for-loop.md), [ASSOCIATIVE ARRAY](associative-array.md), [OPEN FOR](open-for.md)

---

## 1. What It Is and When

Every `FETCH` (and every `SELECT INTO`) is a round trip between the PL/SQL engine and the SQL engine — a **context switch**. `BULK COLLECT` fetches many rows in one switch, straight into a collection. On 500M rows the difference between one switch per row and one per 10,000 rows is the difference between hours and minutes.

It appears in four places:

| Where | Form |
| --- | --- |
| Implicit query | `SELECT ... BULK COLLECT INTO <coll>` |
| Explicit cursor | `FETCH <cur> BULK COLLECT INTO <coll> [LIMIT n]` |
| Dynamic SQL | `EXECUTE IMMEDIATE '...' BULK COLLECT INTO <coll>` |
| DML returning | `DELETE ... RETURNING <col> BULK COLLECT INTO <coll>` |

Use it whenever PL/SQL needs to look at the rows. If it does not — if the logic can live in SQL — use no loop at all ([Chapter 1](../course-plan.md)).

## 2. Syntax

```sql
-- into one collection of records
SELECT <cols> BULK COLLECT INTO <coll_of_records> FROM ... ;

-- into several scalar collections, in select-list order
SELECT <c1>, <c2> BULK COLLECT INTO <coll1>, <coll2> FROM ... ;

-- explicit cursor, batched
FETCH <cursor> BULK COLLECT INTO <coll> [LIMIT <n>];

-- dynamic
EXECUTE IMMEDIATE <sql> BULK COLLECT INTO <coll> [USING <binds>];

-- DML
{INSERT | UPDATE | DELETE} ... RETURNING <cols> BULK COLLECT INTO <coll>;
```

The target collection can be an associative array, nested table or varray (a varray must be large enough). It is always filled densely from index 1, and its previous contents are discarded.

## 3. Examples on the Course Schema

### 3a. A known-small result — no cursor, no LIMIT

```sql
DECLARE
  TYPE t_order_rec IS RECORD (order_id orders.order_id%TYPE, amount orders.amount%TYPE);
  TYPE t_orders    IS TABLE OF t_order_rec;
  l_orders t_orders;
BEGIN
  SELECT order_id, amount BULK COLLECT INTO l_orders
  FROM   orders
  WHERE  customer_id = :id AND status = 'NEW';        -- a few dozen rows per customer

  FOR i IN 1 .. l_orders.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(l_orders(i).order_id || ': ' || l_orders(i).amount);
  END LOOP;
END;
```

Note: no `NO_DATA_FOUND` when the result is empty — `l_orders.COUNT = 0`, that is all.

### 3b. Several scalar collections

```sql
DECLARE
  TYPE t_ids  IS TABLE OF orders.order_id%TYPE;
  TYPE t_amts IS TABLE OF orders.amount%TYPE;
  l_ids  t_ids;
  l_amts t_amts;
BEGIN
  SELECT order_id, amount BULK COLLECT INTO l_ids, l_amts
  FROM   orders WHERE customer_id = :id;
END;
```

Records (3a) keep the row together; parallel scalar collections are what older `FORALL` code needed. Since 11g `FORALL` handles record fields, so prefer records.

### 3c. The canonical batched loop — cursor + LIMIT

```sql
DECLARE
  CURSOR c IS SELECT order_id, customer_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;
    -- process l_rows(1 .. COUNT) in memory, then FORALL the changes back
    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders SET status = 'SEEN' WHERE order_id = l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
END;
```

`c%ROWTYPE` as the element type means adding a column to the cursor never breaks the collection declaration. See [LIMIT](limit.md) for the `%NOTFOUND` trap.

### 3d. RETURNING BULK COLLECT — know what you just changed

```sql
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_deleted t_ids;
BEGIN
  DELETE FROM orders
  WHERE  status = 'CANCELLED' AND order_date < ADD_MONTHS(SYSDATE, -36)
  RETURNING order_id BULK COLLECT INTO l_deleted;

  FORALL i IN 1 .. l_deleted.COUNT
    INSERT INTO orders_purge_log (order_id, purged_at) VALUES (l_deleted(i), SYSTIMESTAMP);
END;
```

Also works after a `FORALL` — `FORALL ... UPDATE ... RETURNING ... BULK COLLECT INTO` collects across all iterations.

### 3e. Dynamic SQL

```sql
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT order_id FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(:tab) || ' WHERE status = :1'
    BULK COLLECT INTO l_ids
    USING 'NEW';
END;
```

This form has no `LIMIT`. For large dynamic results use [OPEN FOR](open-for.md) + `FETCH ... BULK COLLECT ... LIMIT`.

## 4. What Breaks at Scale

- **PGA.** `SELECT ... BULK COLLECT INTO` with no cursor brings *everything* into session memory. 500M rows × 50 bytes = 25 GB → `ORA-04030` or `ORA-04036` (PGA_AGGREGATE_LIMIT). Anything whose size you do not know in advance goes through a cursor with `LIMIT`.
- **The batch is the unit of everything.** Fetch size, `FORALL` size, commit frequency ([COMMIT](commit.md)), error scope ([SAVE EXCEPTIONS](save-exceptions.md)) — all follow the `LIMIT`. 1,000–10,000 is the range; larger batches stop paying off and start costing memory.
- **Diminishing returns.** Switch overhead is fixed per fetch; going from 1 → 100 rows per fetch saves ~99% of it, 100 → 10,000 saves most of the remaining 1%. The cursor FOR loop already gives you 100 for free ([CURSOR FOR LOOP](cursor-for-loop.md)) — for read-only loops that may be enough.
- **LOBs.** A collection of rows with LOB columns holds LOB locators, not the data; fetching them in bulk is fine, but reading each LOB afterwards is a round trip per row.

## 5. Pitfalls

1. `NO_DATA_FOUND` is **not** raised on an empty result. Check `COUNT = 0`.
2. `EXIT WHEN c%NOTFOUND` placed right after a `FETCH ... LIMIT` — drops the last, partial batch. Use `EXIT WHEN coll.COUNT = 0`, or test `%NOTFOUND` after the processing.
3. The collection is **overwritten** on every fetch, not appended. If you need to accumulate, copy the elements out.
4. `BULK COLLECT INTO` a varray with a `LIMIT` smaller than the result → `ORA-22165`. Use nested tables or associative arrays.
5. `SELECT ... BULK COLLECT` in a loop over a "parent" cursor — the N+1 pattern in bulk clothing. Join instead.
6. Column order must match the collection order in the multi-collection form (3b). A record type with `%ROWTYPE` avoids that class of bug.
7. A collection declared at package level keeps its memory until you `DELETE` it — see [COLLECTION METHODS](collection-methods.md).

## 6. Related

- [LIMIT](limit.md) — the batch size and the loop pattern.
- [FORALL](forall.md) — the write side.
- [CURSOR FOR LOOP](cursor-for-loop.md) — the implicit 100-row prefetch.
- [ASSOCIATIVE ARRAY](associative-array.md) / [COLLECTION METHODS](collection-methods.md) — what you are collecting into.
- [OPEN FOR](open-for.md) — batched dynamic SQL.