When the FORALL statement is used without the SAVE EXCEPTIONS clause and an exception is raised by a DML query, the whole
operation is rolled back and the exception goes unhandled. Instead of relying on this default behavior, it is better to always use the SAVE
EXCEPTIONS clause and explicitly handle exceptions in a ORA-24381 handler.
Noncompliant code example
CREATE TABLE my_table(
id NUMBER(10) NOT NULL
);
DECLARE
TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
my_table_ids my_table_id_type := my_table_id_type();
BEGIN
FOR i IN 1 .. 10 LOOP
my_table_ids.EXTEND;
my_table_ids(my_table_ids.LAST) := i;
END LOOP;
-- Cause the failure
my_table_ids(10) := NULL;
FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST -- Noncompliant
INSERT INTO my_table
VALUES (my_table_ids(i));
END;
/
SELECT COUNT(*) FROM my_table;
DROP TABLE my_table;
Compliant solution
-- ...
DECLARE
TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
my_table_ids my_table_id_type := my_table_id_type();
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
FOR i IN 1 .. 10 LOOP
my_table_ids.EXTEND;
my_table_ids(my_table_ids.LAST) := i;
END LOOP;
-- Cause the failure
my_table_ids(10) := NULL;
FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST SAVE EXCEPTIONS
INSERT INTO my_table
VALUES (my_table_ids(i));
EXCEPTION
WHEN bulk_errors THEN
-- Explicitly rollback the whole transaction,
-- or handle each exception individually by looping over SQL%BULK_EXCEPTIONS
ROLLBACK;
END;
/
-- ...
