PL/SQL BULK COLLECT and FORALL: A Practical Performance Guide

A nightly job that processes 200,000 invoices row by row can take an hour. The same job rewritten with BULK COLLECT and FORALL often finishes in a few minutes. The difference is not magic — it comes from reducing the number of times Oracle has to switch between the PL/SQL engine and the SQL engine.

This guide explains when bulk processing helps, how to write it safely for large tables, and how to handle errors without losing an entire batch.

1. Why row-by-row loops are slow

PL/SQL code runs in the PL/SQL engine, while SELECT, INSERT, UPDATE and DELETE statements run in the SQL engine. Every time a loop executes a SQL statement for one row, Oracle performs a context switch between the two engines. One switch is cheap; hundreds of thousands of them are not.

Bulk processing moves many rows per switch:

• BULK COLLECT fetches many rows from SQL into a PL/SQL collection in one call.
• FORALL sends many DML operations from a collection to SQL in one call.

2. First ask: can this be one SQL statement?

Before writing any loop, check whether the logic can be expressed as a single INSERT ... SELECT, UPDATE, or MERGE. A single set-based statement is almost always faster than any PL/SQL loop, bulk or not. Use bulk processing when you genuinely need procedural logic per row: calling APIs, complex validation, logging individual failures, or committing in batches.

3. Basic BULK COLLECT

DECLARE
  TYPE t_ids IS TABLE OF invoices.invoice_id%TYPE;
  l_ids t_ids;
BEGIN
  SELECT invoice_id
    BULK COLLECT INTO l_ids
    FROM invoices
   WHERE status = 'PENDING';

  DBMS_OUTPUT.PUT_LINE(l_ids.COUNT || ' invoices loaded');
END;

This is fine for small result sets. For large tables, loading everything at once can use a lot of PGA memory, so use the LIMIT clause.

4. BULK COLLECT with LIMIT

DECLARE
  CURSOR c_inv IS
    SELECT invoice_id, amount
      FROM invoices
     WHERE status = 'PENDING';

  TYPE t_inv IS TABLE OF c_inv%ROWTYPE;
  l_rows t_inv;
BEGIN
  OPEN c_inv;
  LOOP
    FETCH c_inv BULK COLLECT INTO l_rows LIMIT 500;
    EXIT WHEN l_rows.COUNT = 0;

    FOR i IN 1 .. l_rows.COUNT LOOP
      NULL; -- per-row logic here
    END LOOP;
  END LOOP;
  CLOSE c_inv;
END;

Two details matter:

• Use EXIT WHEN l_rows.COUNT = 0, not EXIT WHEN c_inv%NOTFOUND immediately after the fetch. With a limit, %NOTFOUND becomes true on the final partial batch, and exiting there skips the last rows.
• A limit between 100 and 1,000 is a sensible starting point. Larger values rarely add speed but do add memory use.

5. FORALL for fast DML

FORALL i IN 1 .. l_rows.COUNT
  UPDATE invoices
     SET status    = 'POSTED',
         posted_on = SYSDATE
   WHERE invoice_id = l_rows(i).invoice_id;

FORALL is not a loop. It is a single statement that binds the whole collection and sends it to the SQL engine once. It can contain only one DML statement. Use SQL%BULK_ROWCOUNT(i) afterwards if you need to know how many rows each element affected.

6. Handle errors with SAVE EXCEPTIONS

Without special handling, one failing row stops the whole FORALL and rolls back that statement. SAVE EXCEPTIONS lets the remaining rows continue and reports every failure at the end through error ORA-24381:

DECLARE
  CURSOR c_inv IS
    SELECT invoice_id, amount
      FROM invoices
     WHERE status = 'PENDING';

  TYPE t_inv IS TABLE OF c_inv%ROWTYPE;
  l_rows t_inv;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  OPEN c_inv;
  LOOP
    FETCH c_inv BULK COLLECT INTO l_rows LIMIT 500;
    EXIT WHEN l_rows.COUNT = 0;

    BEGIN
      FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
        INSERT INTO invoice_postings (invoice_id, amount, posted_on)
        VALUES (l_rows(i).invoice_id, l_rows(i).amount, SYSDATE);
    EXCEPTION
      WHEN e_bulk_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          INSERT INTO batch_errors (invoice_id, error_text, logged_on)
          VALUES (
            l_rows(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).invoice_id,
            SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE),
            SYSDATE);
        END LOOP;
    END;

    COMMIT;
  END LOOP;
  CLOSE c_inv;
END;

ERROR_INDEX points to the position in the collection that failed, so you can log the exact business key. Note the minus sign in SQLERRM(-...): ERROR_CODE is stored as a positive number.

7. Committing inside the loop

Committing after each batch keeps undo usage small and lets a long job restart from where it stopped, but it also means a failure leaves earlier batches committed. Decide deliberately:

• For posting jobs, design the process to be restartable — select only rows that are still pending, as in the example above.
• If the whole batch must succeed or fail together, commit once at the end instead.
• Avoid committing inside a loop that is still fetching from a cursor on a table you are modifying heavily, as this increases the risk of ORA-01555 snapshot too old on long runs.

8. Sparse collections: INDICES OF and VALUES OF

If you delete elements from a collection (for example, rows that failed validation), the collection becomes sparse and FORALL i IN 1 .. l_rows.COUNT will fail. Use FORALL i IN INDICES OF l_rows to process only the elements that still exist.

9. Measure before and after

Always test with realistic data volumes. Record elapsed time, rows processed and redo generated for the old and new versions. In our experience, converting row-by-row DML to FORALL commonly reduces run time by several times, but the real gain depends on indexes, triggers and what the per-row logic does.

Quick checklist

• Considered a single SQL statement first
• Used LIMIT for large result sets
• Exit condition based on COUNT = 0
• SAVE EXCEPTIONS with error logging by business key
• Commit strategy decided and job made restartable
• INDICES OF used for sparse collections
• Performance measured with production-like volumes

Need help tuning Oracle PL/SQL?

AbairTech works on Oracle Database and PL/SQL development, batch processing and performance tuning for ERP, POS and APEX applications. See Oracle Database & PL/SQL Development Services.