SAVE EXCEPTIONS and SQL%BULK_EXCEPTION
We saw how the
FORALL syntax allows us to perform bulk DML operations.
But what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current bulk operation is rolled back.
If there is an exception handler, the work done prior to the exception is kept, but no more processing is done.
So instead we should use the
SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue past them.
We can subsequently look at the exceptions by referencing the
SQL%BULK_EXCEPTION cursor attribute.
create the following table.
CREATE TABLE exception_test (
id NUMBER(10) NOT NULL
);
The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL.
Since the above table does not allow nulls, these rows will result in an exception.
The
SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete.
In this case, the exception handler just loops through the
SQL%BULK_EXCEPTION cursor attribute to see what errors occurred.
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
END LOOP;
-- Cause a failure.
l_tab(50).id := NULL;
l_tab(51).id := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO exception_test
VALUES l_tab(i);
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END;
/
Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()
PL/SQL procedure successfully completed.
SQL>
As expected the errors were trapped. If we query the table we can see that 98 rows were inserted correctly.
SELECT COUNT(*)
FROM exception_test;
COUNT(*)
----------
98
1 row selected.
SQL>
