BULK COLLECT with LIMIT
Oracle Fusion / EBS · Technical · PL/SQL
GeneralHigh confidence
Caps each BULK COLLECT fetch to a fixed batch size so memory usage stays bounded.
Grounded in: Curated Oracle knowledge layer — PL/SQL
How it works
A plain BULK COLLECT loads the entire result set into memory at once — for millions of rows, that's exactly the memory blowup BULK COLLECT is otherwise meant to avoid. Adding LIMIT to the FETCH caps each batch to a fixed size, processed in a loop until the cursor is exhausted (an empty batch signals the end), trading a few more round trips for bounded PGA usage.
How it works
DECLARE
CURSOR c_all_emp IS SELECT employee_id FROM employees;
TYPE t_id_tab IS TABLE OF employees.employee_id%TYPE;
v_ids t_id_tab;
BEGIN
OPEN c_all_emp;
LOOP
FETCH c_all_emp BULK COLLECT INTO v_ids LIMIT 1000;
EXIT WHEN v_ids.COUNT = 0;
FORALL v_i IN 1..v_ids.COUNT
UPDATE employees SET last_reviewed_date = SYSDATE WHERE employee_id = v_ids(v_i);
END LOOP;
CLOSE c_all_emp;
END;
/Related Questions
Have a follow-up, or a different question?
Continue in Ask Oracle AI