Oracle PL/SQL Bulk Operations: BULK COLLECT and FORALL

If you are processing large volumes of data in PL/SQL using row-by-row cursor loops, you are almost certainly doing it the slow way. The difference between row-by-row processing and bulk operations can be enormous — 10x, 50x, even 100x faster — because bulk operations dramatically reduce the number of context switches between the PL/SQL engine and the SQL engine.

In this guide, we cover BULK COLLECT for fast data retrieval and FORALL for fast bulk DML, along with the patterns that make them practical in real production code.

Why Row-by-Row Is Slow

Every time a PL/SQL program executes a SQL statement, there is a context switch between two Oracle engines: the PL/SQL engine and the SQL engine. For a loop that processes 10,000 rows one at a time, this means 10,000 context switches for the FETCH and another 10,000 for each DML operation — 20,000+ round trips.

Bulk operations handle this in a single batch, dramatically reducing the overhead.

BULK COLLECT: Fetching Multiple Rows at Once

BULK COLLECT INTO fetches multiple rows from a query directly into a PL/SQL collection in a single database call:

DECLARE
  -- Define collection types
  TYPE num_tab   IS TABLE OF employees.employee_id%TYPE;
  TYPE str_tab   IS TABLE OF employees.last_name%TYPE;
  TYPE num_tab2  IS TABLE OF employees.salary%TYPE;
  
  l_emp_ids   num_tab;
  l_names     str_tab;
  l_salaries  num_tab2;
BEGIN
  -- Fetch all matching rows in one database call
  SELECT employee_id, last_name, salary
  BULK COLLECT INTO l_emp_ids, l_names, l_salaries
  FROM employees
  WHERE department_id = 20
  ORDER BY last_name;
  
  -- Process the data in memory (fast: no more DB round trips)
  FOR i IN 1..l_emp_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      l_names(i) || ' (ID: ' || l_emp_ids(i) || ') — Salary: ' || l_salaries(i)
    );
  END LOOP;
END;

Using %ROWTYPE for Cleaner Bulk Collect

Instead of separate collections per column, use a table of records with %ROWTYPE:

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  l_employees emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO l_employees
  FROM employees
  WHERE department_id = 20;
  
  FOR i IN 1..l_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      l_employees(i).last_name || ' — ' || l_employees(i).salary
    );
  END LOOP;
END;

LIMIT: Controlling Memory with Batch Processing

For very large tables, fetching everything at once with BULK COLLECT can consume too much memory. The LIMIT clause lets you process in batches:

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  l_employees emp_tab;
  l_batch_size CONSTANT NUMBER := 1000;
  
  CURSOR emp_cur IS
    SELECT * FROM employees ORDER BY employee_id;
    
BEGIN
  OPEN emp_cur;
  
  LOOP
    -- Fetch up to 1000 rows at a time
    FETCH emp_cur BULK COLLECT INTO l_employees LIMIT l_batch_size;
    
    -- Process this batch
    FOR i IN 1..l_employees.COUNT LOOP
      -- Process each employee record
      NULL;
    END LOOP;
    
    -- Exit when fewer rows than LIMIT were fetched (means we're done)
    EXIT WHEN l_employees.COUNT < l_batch_size;
  END LOOP;
  
  CLOSE emp_cur;
END;

A batch size of 500–1000 rows is usually a good balance between memory usage and performance. Tune based on your row size and available PGA memory.

FORALL: Bulk DML Operations

FORALL sends an entire collection to the SQL engine in one call for DML operations (INSERT, UPDATE, DELETE). This is the DML counterpart to BULK COLLECT:

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  l_emp_ids  num_tab;
  l_salaries num_tab;
BEGIN
  -- Load data to process
  SELECT employee_id, salary
  BULK COLLECT INTO l_emp_ids, l_salaries
  FROM employees
  WHERE department_id = 20;
  
  -- Multiply all salaries by 1.10 in one bulk operation
  -- Instead of: FOR i IN 1..l_emp_ids.COUNT LOOP UPDATE ... END LOOP;
  FORALL i IN 1..l_emp_ids.COUNT
    UPDATE employees
    SET    salary = l_salaries(i) * 1.10
    WHERE  employee_id = l_emp_ids(i);
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated.');
END;

The FORALL sends all the UPDATE statements as a batch to the SQL engine — one context switch instead of thousands.

FORALL with INSERT

DECLARE
  TYPE rec_tab IS TABLE OF new_employees%ROWTYPE;
  l_new_emps rec_tab;
BEGIN
  -- Fetch employees from staging table
  SELECT * BULK COLLECT INTO l_new_emps
  FROM staging_employees
  WHERE processed = 'N';
  
  -- Bulk insert into target table
  FORALL i IN 1..l_new_emps.COUNT
    INSERT INTO new_employees VALUES l_new_emps(i);
  
  -- Mark as processed
  FORALL i IN 1..l_new_emps.COUNT
    UPDATE staging_employees
    SET processed = 'Y'
    WHERE employee_id = l_new_emps(i).employee_id;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE(l_new_emps.COUNT || ' employees processed.');
END;

Error Handling with SAVE EXCEPTIONS

By default, FORALL stops at the first error. The SAVE EXCEPTIONS clause lets FORALL continue processing remaining rows even when some fail, collecting all errors for review:

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  l_ids    num_tab := num_tab(101, 999, 102, 103, 888); -- 999 and 888 don't exist
  
  bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
  FORALL i IN 1..l_ids.COUNT SAVE EXCEPTIONS
    DELETE FROM employees WHERE employee_id = l_ids(i);
  
  COMMIT;
  
EXCEPTION
  WHEN bulk_errors THEN
    DBMS_OUTPUT.PUT_LINE('Errors encountered: ' || SQL%BULK_EXCEPTIONS.COUNT);
    
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Error at index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || 
        ': ORA-' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
      );
    END LOOP;
    
    COMMIT; -- Commit successful deletes even with partial failures
END;

SQL%BULK_EXCEPTIONS is a collection of error records, each containing ERROR_INDEX (which element failed) and ERROR_CODE (the Oracle error number). This gives you complete visibility into which rows succeeded and which failed.

Combining BULK COLLECT + FORALL: The Complete Pattern

DECLARE
  TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
  l_emp_ids emp_id_tab;
  l_batch_size CONSTANT NUMBER := 500;
  
  CURSOR inactive_emp_cur IS
    SELECT employee_id FROM employees
    WHERE last_login < SYSDATE - 365
    AND   status = 'ACTIVE';
    
BEGIN
  OPEN inactive_emp_cur;
  
  LOOP
    FETCH inactive_emp_cur BULK COLLECT INTO l_emp_ids LIMIT l_batch_size;
    EXIT WHEN l_emp_ids.COUNT = 0;
    
    -- Bulk update all fetched records at once
    FORALL i IN 1..l_emp_ids.COUNT
      UPDATE employees
      SET status = 'INACTIVE', deactivated_date = SYSDATE
      WHERE employee_id = l_emp_ids(i);
    
    COMMIT; -- Commit each batch to avoid large rollback segments
    
    DBMS_OUTPUT.PUT_LINE('Processed batch of ' || l_emp_ids.COUNT || ' employees.');
  END LOOP;
  
  CLOSE inactive_emp_cur;
END;

This pattern — batch BULK COLLECT in a loop + FORALL DML + batch COMMIT — is the gold standard for large-scale data processing in PL/SQL. It is memory-efficient, fast, and committs frequently to keep rollback manageable.

Performance Comparison

Approach	100K Rows	Relative Speed
Row-by-row cursor loop	Baseline	1x
BULK COLLECT + row-by-row update	~2–3x faster	Better
BULK COLLECT + FORALL	~10–50x faster	Much better
Pure SQL (single UPDATE)	Fastest	Best if no PL/SQL logic needed

If you can express the operation as a single SQL statement, always prefer that over PL/SQL loops. Use BULK COLLECT + FORALL when the logic is too complex for a single SQL statement.

Conclusion

BULK COLLECT and FORALL are essential tools for any PL/SQL developer who works with significant data volumes. The performance improvement over row-by-row processing is dramatic and consistent. Once you adopt the bulk operation pattern, you will never go back to writing slow cursor loops for data processing tasks.

The key principles: always LIMIT your BULK COLLECT for large tables, use FORALL instead of loops for DML, use SAVE EXCEPTIONS when partial failures are acceptable, and commit by batch to keep rollback segments manageable.