SAVE EXCEPTIONS in FORALL
ExplanationOracle Fusion / EBS · Technical · PL/SQLHigh confidenceGenerated
Lets FORALL keep processing every element despite individual failures, then report them all at once.
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
GroundingGenerated
Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.