BULK COLLECT & FORALL

Every time PL/SQL sends a SQL statement to the SQL engine, it performs a context switch. For row-by-row processing in a loop, this switch happens once per row — potentially thousands of times. BULK COLLECT and FORALL are Oracle's mechanisms for batching these switches to dramatically reduce overhead.

Why Context Switches Hurt

-- SLOW: one context switch per employee (row-by-row antipattern)
DECLARE
    CURSOR c_emp IS SELECT employee_id, salary FROM employees;
    v_emp_id  employees.employee_id%TYPE;
    v_salary  employees.salary%TYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp_id, v_salary;
        EXIT WHEN c_emp%NOTFOUND;

        -- Each UPDATE is a separate context switch to SQL engine
        UPDATE employees SET salary = salary * 1.05
        WHERE  employee_id = v_emp_id;
    END LOOP;
    CLOSE c_emp;
    COMMIT;
END;
/
-- 107 employees = 107 UPDATEs = 107 context switches

BULK COLLECT INTO

BULK COLLECT fetches an entire result set (or a batch) into a collection in one SQL call:

DECLARE
    TYPE t_id_list   IS TABLE OF employees.employee_id%TYPE;
    TYPE t_sal_list  IS TABLE OF employees.salary%TYPE;
    TYPE t_name_list IS TABLE OF VARCHAR2(100);

    v_ids     t_id_list;
    v_sals    t_sal_list;
    v_names   t_name_list;
BEGIN
    -- ONE context switch fetches all rows
    SELECT employee_id, salary, first_name || ' ' || last_name
    BULK COLLECT INTO v_ids, v_sals, v_names
    FROM   employees
    WHERE  department_id = 80
    ORDER BY salary DESC;

    DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_ids.COUNT || ' employees');

    FOR i IN 1..v_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_names(i) || ': $' || v_sals(i));
    END LOOP;
END;
/

BULK COLLECT with LIMIT

For very large tables, fetching everything at once consumes too much PGA memory. Use LIMIT to process in batches:

DECLARE
    TYPE t_emp_list IS TABLE OF employees%ROWTYPE;
    v_emps      t_emp_list;
    v_batch     PLS_INTEGER := 50;  -- rows per batch

    CURSOR c_all_emps IS
        SELECT * FROM employees ORDER BY employee_id;
BEGIN
    OPEN c_all_emps;
    LOOP
        -- Fetch up to v_batch rows per iteration
        FETCH c_all_emps BULK COLLECT INTO v_emps LIMIT v_batch;
        EXIT WHEN v_emps.COUNT = 0;

        -- Process this batch
        FOR i IN 1..v_emps.COUNT LOOP
            -- ... business logic ...
            NULL;
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Processed batch of ' || v_emps.COUNT);
        COMMIT;  -- commit after each batch

        EXIT WHEN v_emps.COUNT < v_batch;  -- last partial batch
    END LOOP;
    CLOSE c_all_emps;
END;
/

FORALL — Bulk DML

FORALL sends a batch of DML statements to the SQL engine in one round-trip:

DECLARE
    TYPE t_id_list  IS TABLE OF employees.employee_id%TYPE;
    TYPE t_sal_list IS TABLE OF employees.salary%TYPE;

    v_ids    t_id_list;
    v_sals   t_sal_list;
BEGIN
    -- Collect the employees to update
    SELECT employee_id, salary * 1.10
    BULK COLLECT INTO v_ids, v_sals
    FROM   employees
    WHERE  department_id = 60;

    -- ONE context switch for ALL updates
    FORALL i IN 1..v_ids.COUNT
        UPDATE employees
        SET    salary = v_sals(i)
        WHERE  employee_id = v_ids(i);

    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows.');
    COMMIT;
END;
/

FORALL Index Ranges

-- All elements
FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE employee_id = v_ids(i);

-- Subset using INDICES OF (for sparse collections)
FORALL i IN INDICES OF v_ids
    UPDATE employees SET salary = v_sals(i)
    WHERE  employee_id = v_ids(i);

-- Only elements where condition holds
FORALL i IN VALUES OF v_active_indices
    UPDATE employees SET status = 'ACTIVE'
    WHERE  employee_id = v_ids(i);

SAVE EXCEPTIONS

By default, FORALL stops at the first DML error. SAVE EXCEPTIONS continues processing all rows and collects errors for inspection:

DECLARE
    TYPE t_id_list  IS TABLE OF employees.employee_id%TYPE;
    TYPE t_sal_list IS TABLE OF employees.salary%TYPE;

    v_ids    t_id_list  := t_id_list(100, 101, 999, 103);  -- 999 doesn't exist
    v_sals   t_sal_list := t_sal_list(25000, 18000, 0, 14000);

    e_bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);  -- ORA-24381
BEGIN
    FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
        UPDATE employees
        SET    salary = v_sals(i)
        WHERE  employee_id = v_ids(i);

    COMMIT;

EXCEPTION
    WHEN e_bulk_errors THEN
        DBMS_OUTPUT.PUT_LINE('Bulk errors: ' || 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 successful rows, skip failed ones
        COMMIT;
END;
/

SQL%BULK_ROWCOUNT

After FORALL, SQL%BULK_ROWCOUNT(i) tells you how many rows the i-th DML statement affected:

DECLARE
    TYPE t_dept_list IS TABLE OF employees.department_id%TYPE;
    v_depts  t_dept_list := t_dept_list(60, 80, 90);
BEGIN
    FORALL i IN 1..v_depts.COUNT
        UPDATE employees
        SET    salary = salary * 1.03
        WHERE  department_id = v_depts(i);

    FOR i IN 1..v_depts.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Dept ' || v_depts(i) ||
            ': ' || SQL%BULK_ROWCOUNT(i) || ' rows updated'
        );
    END LOOP;

    COMMIT;
END;
/

Combining BULK COLLECT and FORALL

The high-performance pattern for ETL or mass updates:

CREATE OR REPLACE PROCEDURE apply_annual_raise(p_pct IN NUMBER) IS
    TYPE t_id_list  IS TABLE OF employees.employee_id%TYPE;
    TYPE t_sal_list IS TABLE OF employees.salary%TYPE;

    v_ids    t_id_list;
    v_sals   t_sal_list;

    CURSOR c_emps IS
        SELECT employee_id, ROUND(salary * (1 + p_pct/100), 2)
        FROM   employees
        WHERE  salary < 20000;
BEGIN
    -- Fetch all eligible employees in one call
    OPEN c_emps;
    FETCH c_emps BULK COLLECT INTO v_ids, v_sals;
    CLOSE c_emps;

    -- Apply all updates in one call
    FORALL i IN 1..v_ids.COUNT
        UPDATE employees
        SET    salary = v_sals(i)
        WHERE  employee_id = v_ids(i);

    DBMS_OUTPUT.PUT_LINE(
        'Applied ' || p_pct || '% raise to ' ||
        v_ids.COUNT || ' employees.'
    );
    COMMIT;
END apply_annual_raise;
/

BEGIN apply_annual_raise(5); END;
/

Performance Comparison

Use a single SQL statement when possible. Use BULK COLLECT + FORALL when you need PL/SQL logic per row that cannot be expressed in SQL.

Summary

• Context switches between the PL/SQL and SQL engines are expensive — minimize them.
• BULK COLLECT INTO fetches multiple rows in one SQL call.
• Use LIMIT with BULK COLLECT to control memory usage on large result sets.
• FORALL sends bulk DML in one SQL call — dramatically faster than looping.
• SAVE EXCEPTIONS continues after DML errors; inspect SQL%BULK_EXCEPTIONS.
• SQL%BULK_ROWCOUNT(i) reports rows affected by the i-th FORALL statement.
• Typical bulk approach: BULK COLLECT into collections, process with PL/SQL logic, FORALL for the DML.