Oracle 19c PL/SQL Bulk Processing: BULK COLLECT and FORALL

Oracle 19c PL/SQL bulk processing reduces the cost of moving between the PL/SQL engine and the SQL engine. The main tools are BULK COLLECT for fetching rows in batches and FORALL for sending many DML operations to SQL in one statement context.

Bulk syntax is not automatically faster in every program. A developer should first measure the row count, SQL work, memory use, redo generation, locking, and commit duration. This Oracle Database 19c and later guide shows how to choose a batch size, avoid common loop bugs, preserve row-level errors, and design a job that can be restarted safely.

When bulk processing is the right solution

• Use a set-based SQL statement when one SQL statement can express the complete transformation.
• Use BULK COLLECT and FORALL when procedural rules are required and row-by-row SQL calls are measurable bottlenecks.
• Use a cursor with LIMIT when the result set is too large to safely load into PGA memory at once.
• Use a restartable batch design when the job can run for minutes or hours, can be interrupted, or must be audited.

Before changing code, compare a row-by-row baseline with a bulk version. A faster loop that holds millions of rows in memory or commits unpredictably may be a production regression.

First choice: set-based SQL

Bulk PL/SQL is often unnecessary when the rule can be expressed in SQL. A single statement usually gives the optimizer more information and avoids procedural context switches.

UPDATE order_line ol
   SET ol.net_amount = ol.quantity * ol.unit_price
 WHERE ol.status = 'OPEN'
   AND ol.net_amount IS NULL;

If the calculation requires procedural validation, external calls, or several independent decisions, bulk PL/SQL may be appropriate. Keep the SQL work inside SQL and reserve PL/SQL for the part that truly needs it.

Row-by-row baseline

This baseline is easy to understand but may perform one SQL update per fetched row.

DECLARE
  CURSOR c_lines IS
    SELECT line_id, quantity, unit_price
      FROM order_line
     WHERE status = 'OPEN';
BEGIN
  FOR r_line IN c_lines LOOP
    UPDATE order_line
       SET net_amount = r_line.quantity * r_line.unit_price
     WHERE line_id = r_line.line_id;
  END LOOP;

  COMMIT;
END;

Do not assume that replacing this code with FORALL is enough. Measure both versions and inspect the execution plan and waits for the real workload.

Fetch a bounded batch with BULK COLLECT and LIMIT

The safest general pattern uses an explicit cursor, a collection, and a finite LIMIT. The collection is cleared and refilled on every fetch.

DECLARE
  CURSOR c_lines IS
    SELECT line_id, quantity, unit_price
      FROM order_line
     WHERE status = 'OPEN'
     ORDER BY line_id;

  TYPE t_line_tab IS TABLE OF c_lines%ROWTYPE;
  l_lines t_line_tab;
  c_limit CONSTANT PLS_INTEGER := 500;
BEGIN
  OPEN c_lines;
  LOOP
    FETCH c_lines BULK COLLECT INTO l_lines LIMIT c_limit;
    EXIT WHEN l_lines.COUNT = 0;

    FORALL i IN 1 .. l_lines.COUNT
      UPDATE order_line
         SET net_amount = l_lines(i).quantity * l_lines(i).unit_price
       WHERE line_id = l_lines(i).line_id;
  END LOOP;
  CLOSE c_lines;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    IF c_lines%ISOPEN THEN
      CLOSE c_lines;
    END IF;
    RAISE;
END;

The EXIT WHEN l_lines.COUNT = 0 condition is important. It handles the final fetch correctly when the number of rows is an exact multiple of the limit. Do not exit only when COUNT < c_limit without understanding the extra empty fetch.

FORALL for bulk DML

FORALL sends a DML statement for a range of collection indexes. It is not a loop that executes arbitrary PL/SQL statements; only one DML statement is allowed in its body.

DECLARE
  TYPE t_id_tab IS TABLE OF order_line.line_id%TYPE;
  l_ids t_id_tab := t_id_tab(101, 102, 103);
BEGIN
  FORALL i IN INDICES OF l_ids
    UPDATE order_line
       SET status = 'READY'
     WHERE line_id = l_ids(i);

  DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
  COMMIT;
END;

Use INDICES OF when the collection can be sparse. Use VALUES OF when a separate index collection defines which rows should be processed.

FORALL with SAVE EXCEPTIONS

Without SAVE EXCEPTIONS, one failed DML operation can stop the entire FORALL statement. With it, Oracle records failures in SQL%BULK_EXCEPTIONS and raises ORA-24381 after processing the batch.

DECLARE
  TYPE t_id_tab IS TABLE OF order_line.line_id%TYPE;
  l_ids t_id_tab := t_id_tab(101, 999999, 103);
  l_errors PLS_INTEGER;
BEGIN
  FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
    UPDATE order_line
       SET status = 'READY'
     WHERE line_id = l_ids(i);

  DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24381 THEN
      l_errors := SQL%BULK_EXCEPTIONS.COUNT;
      FOR i IN 1 .. l_errors LOOP
        DBMS_OUTPUT.PUT_LINE(
          'Collection index=' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
          ', error=' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
      END LOOP;
    ELSE
      RAISE;
    END IF;
END;

ERROR_INDEX is the collection index, not necessarily a business key. Use it to retrieve the original input value and then log the business identifier.

Map bulk errors to business keys

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24381 THEN
      FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        DECLARE
          l_index PLS_INTEGER := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
          l_code  PLS_INTEGER := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
        BEGIN
          INSERT INTO batch_error_log (
            batch_id, business_id, oracle_error_code, created_at
          ) VALUES (
            l_batch_id,
            l_lines(l_index).line_id,
            l_code,
            SYSTIMESTAMP
          );
        END;
      END LOOP;
      COMMIT;
    ELSE
      RAISE;
    END IF;

Do not store only the numeric error code. Record the batch identifier, business key, collection index, error text, processing timestamp, and input source so support staff can reproduce the failure.

Use SAVE EXCEPTIONS deliberately

Partial success is useful for data-cleansing jobs, but it is dangerous for an all-or-nothing financial transaction. Decide explicitly whether the batch should:

• Rollback all rows when any row fails.
• Commit valid rows and store rejected rows for correction.
• Stop after a threshold of failures.
• Move failed rows to a quarantine table and continue.

The transaction policy belongs in the application design, not as an accidental side effect of a WHEN OTHERS handler.

Batch commits and transaction boundaries

Commit frequency is a business and recovery decision. Committing every row increases overhead and makes recovery difficult. Keeping one transaction for millions of rows can increase undo, locks, redo pressure, and rollback time.

DECLARE
  c_commit_every CONSTANT PLS_INTEGER := 20;
  l_batch_count PLS_INTEGER := 0;
BEGIN
  -- Fetch and process one bounded batch here.
  -- Increment only after the batch has completed successfully.
  l_batch_count := l_batch_count + 1;

  IF l_batch_count >= c_commit_every THEN
    COMMIT;
    l_batch_count := 0;
  END IF;
END;

Do not commit inside a reusable API unless the API owns the transaction contract. A procedure called by a larger business transaction should normally leave commit control to its caller.

Restartable processing with a high-water mark

A restartable job records progress using a stable, indexed key. The key must provide deterministic ordering and must not change while the job runs.

DECLARE
  l_last_line_id order_line.line_id%TYPE := :p_last_line_id;
BEGIN
  SELECT line_id
    BULK COLLECT INTO l_ids
    FROM order_line
   WHERE line_id > l_last_line_id
     AND status = 'OPEN'
   ORDER BY line_id
   FETCH FIRST 500 ROWS ONLY;

  IF l_ids.COUNT > 0 THEN
    FORALL i IN 1 .. l_ids.COUNT
      UPDATE order_line
         SET status = 'PROCESSED'
       WHERE line_id = l_ids(i);

    l_last_line_id := l_ids(l_ids.COUNT);
    COMMIT;
  END IF;
END;

For concurrent workers, use a deliberate claiming strategy such as a status transition or FOR UPDATE SKIP LOCKED. Do not rely on a high-water mark alone if rows can be inserted or updated while workers are running.

Bulk collect with scalar collections

Scalar collections can be convenient when only a few columns are needed. Keep the collection indexes aligned and validate nullability and data types.

DECLARE
  TYPE t_id_tab IS TABLE OF order_line.line_id%TYPE;
  TYPE t_amount_tab IS TABLE OF order_line.net_amount%TYPE;
  l_ids     t_id_tab;
  l_amounts t_amount_tab;
BEGIN
  SELECT line_id, quantity * unit_price
    BULK COLLECT INTO l_ids, l_amounts
    FROM order_line
   WHERE status = 'OPEN'
   FETCH FIRST 500 ROWS ONLY;

  FORALL i IN 1 .. l_ids.COUNT
    UPDATE order_line
       SET net_amount = l_amounts(i)
     WHERE line_id = l_ids(i);
END;

Measure before and after

Measure the same data volume and workload. A simple elapsed-time measurement is useful, but it does not explain why a change helped or hurt.

DECLARE
  l_started TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
  -- Call the procedure under test.
  process_order_lines;

  DBMS_OUTPUT.PUT_LINE(
    'Elapsed seconds=' ||
    EXTRACT(SECOND FROM (SYSTIMESTAMP - l_started))
  );
END;

For production analysis, also inspect SQL Monitor where licensed and enabled, session statistics, execution plans, wait events, redo, undo, PGA usage, row locks, and the number of context switches. Compare total work, not only the fastest small test.

Memory and batch-size tuning

A larger LIMIT reduces fetch calls but increases PGA usage and the amount of work rolled back when a batch fails. Start with a conservative value such as 100, 500, or 1,000, then test with representative row widths and concurrency.

• Wide rows need smaller batches than narrow identifier-only rows.
• Large LOB values can make a seemingly small row set consume substantial memory.
• High concurrency changes the safe PGA and locking profile.
• Use a configuration constant rather than scattering magic numbers through the code.

Do not bulk collect an unbounded result set

-- Risky for a large or untrusted result set:
SELECT line_id, quantity, unit_price
  BULK COLLECT INTO l_lines
  FROM order_line
 WHERE status = 'OPEN';

An unbounded fetch can exhaust session memory or create long pauses. Prefer an explicit cursor with LIMIT, a bounded key range, or a set-based SQL statement.

Exception handling that preserves the root cause

Capture diagnostics before translating an exception. RAISE preserves the original error; a generic replacement can hide the original line and error stack.

EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO batch_error_log (
      batch_id,
      oracle_error_code,
      error_stack,
      error_backtrace,
      created_at
    ) VALUES (
      l_batch_id,
      SQLCODE,
      DBMS_UTILITY.FORMAT_ERROR_STACK,
      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
      SYSTIMESTAMP
    );
    COMMIT;
    RAISE;

For a reusable logging package, see the Oracle 19c PL/SQL exception and error logging guide.

Common bulk-processing failures

• ORA-24381: inspect SQL%BULK_EXCEPTIONS after using SAVE EXCEPTIONS.
• ORA-12899: validate string lengths before the DML or record the rejected business key.
• ORA-01400: check required values and defaults before calling FORALL.
• ORA-00001: identify duplicate keys and decide whether duplicates should be rejected, merged, or skipped.
• ORA-01555: reduce excessive cursor duration, review undo retention, and avoid unnecessary long-running consistent reads.
• High PGA usage: reduce the limit, select fewer columns, and avoid unbounded collections.
• Slow processing: check indexes, execution plans, row locks, commits, triggers, foreign keys, and competing sessions.
• Final rows not processed: ensure the loop exits only after the zero-row fetch.

Related Oracle 19c resources

• Oracle 19c PL/SQL cursors and exception handling – cursor lifecycle, NO_DATA_FOUND, and safe cleanup.
• Oracle 19c CLOB search with DBMS_LOB.INSTR – work with large text values without loading unnecessary data.
• Oracle 19c session identification – correlate long-running jobs with sessions and application context.
• Oracle DML error logging – compare DML error logging with SAVE EXCEPTIONS.
• Oracle Database 19c PL/SQL Language Reference – verify syntax and release-specific behavior.
• Oracle Database 19c Database Development Guide – review transaction, SQL, and development guidance.

Developer checklist

1. Try a set-based SQL solution first.
2. Measure a row-by-row baseline with representative data.
3. Choose a bounded batch size and document why it is safe.
4. Use EXIT WHEN collection.COUNT = 0 with cursor fetches.
5. Use FORALL only for the DML that benefits from batching.
6. Use SAVE EXCEPTIONS only when partial success is acceptable.
7. Map collection indexes back to stable business keys.
8. Define transaction ownership and commit boundaries explicitly.
9. Make long jobs restartable and observable.
10. Capture FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE before re-raising.
11. Test memory, locks, redo, undo, triggers, constraints, and concurrent workers.

Summary: Oracle 19c bulk processing is a design discipline, not merely a syntax replacement. Use SQL when possible, fetch bounded batches, apply DML with FORALL, handle partial failures intentionally, and verify the result with measurements that represent production conditions.