← PL/SQL Topics
Cursors & Bulk Operations
Explain the SAVE EXCEPTIONS clause in FORALL and handling bulk DML errors in PL/SQL
SAVE EXCEPTIONS in FORALL
Oracle Fusion / EBS · Technical · PL/SQL
GeneralHigh confidence
Lets FORALL keep processing every element despite individual failures, then report them all at once.
Grounded in: Curated Oracle knowledge layer — PL/SQL
How it works
By default, FORALL stops at the first element whose DML raises an error and propagates that exception immediately. Adding SAVE EXCEPTIONS makes it instead process every element regardless of individual failures, then raise a single ORA-24381 once the whole statement finishes; the actual list of failures — each one's index and error code — is available afterward via the SQL%BULK_EXCEPTIONS collection for logging or reporting.
How it works
DECLARE
TYPE t_id_tab IS TABLE OF NUMBER;
v_ids t_id_tab := t_id_tab(1, 2, 2, 3); -- 2 is duplicated, will violate a unique key
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
FORALL v_i IN v_ids.FIRST..v_ids.LAST SAVE EXCEPTIONS
INSERT INTO staging_ids (id) VALUES (v_ids(v_i));
EXCEPTION
WHEN bulk_errors THEN
FOR v_i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Row ' || SQL%BULK_EXCEPTIONS(v_i).ERROR_INDEX ||
' failed: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(v_i).ERROR_CODE));
END LOOP;
END;
/Related Questions
Have a follow-up, or a different question?
Continue in Ask Oracle AI