PL/SQL Bulk Operations: BULK COLLECT and FORALL

Why row-by-row processing is slow

In PL/SQL, every interaction with the database (SELECT, INSERT, UPDATE, DELETE) inside a loop causes a context switch between the PL/SQL engine and the SQL engine. For 10,000 rows, that’s 10,000 context switches — which is the primary cause of slow PL/SQL batch jobs.

BULK COLLECT: fetching in arrays

Instead of fetching one row at a time with a cursor loop, BULK COLLECT fetches multiple rows in a single database call:

DECLARE
  TYPE t_emp IS TABLE OF employees%ROWTYPE;
  l_emp t_emp;
BEGIN
  SELECT * BULK COLLECT INTO l_emp
  FROM employees WHERE department_id = 10;
  
  FOR i IN 1..l_emp.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(l_emp(i).last_name);
  END LOOP;
END;

For large tables, use the LIMIT clause to avoid loading millions of rows into memory:

CURSOR c IS SELECT * FROM big_table;
OPEN c;
LOOP
  FETCH c BULK COLLECT INTO l_data LIMIT 1000;
  EXIT WHEN l_data.COUNT = 0;
  -- process l_data
END LOOP;
CLOSE c;

FORALL: batch DML

FORALL executes DML using an array, sending all rows to the SQL engine in one call:

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  l_ids t_ids := t_ids(101, 102, 103, 104, 105);
BEGIN
  FORALL i IN 1..l_ids.COUNT
    UPDATE employees SET salary = salary * 1.1
    WHERE employee_id = l_ids(i);
  COMMIT;
END;

Combining BULK COLLECT and FORALL

The most powerful pattern: collect from source, transform in PL/SQL, insert to target:

BULK COLLECT INTO l_source_data FROM source_table WHERE ...;
-- transform
FORALL i IN 1..l_source_data.COUNT
  INSERT INTO target_table VALUES (l_source_data(i).col1, ...);

SAVE EXCEPTIONS

By default, any error in FORALL aborts the entire batch. Use SAVE EXCEPTIONS to continue and collect errors:

BEGIN
  FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
    INSERT INTO orders VALUES l_data(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Error at index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);
    END LOOP;
END;

Real performance impact

A batch job processing 50,000 rows reduced from 4 minutes 20 seconds (row-by-row) to 8 seconds (BULK COLLECT + FORALL) — a 32× improvement.