Source
SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.
The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised. The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.
save_exceptions.sql
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;
/
SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
The FORALL statement includes the SAVE EXCEPTIONS clause, and the exception handler displays the number of exceptions and their associated error messages. The output from the save_exceptions.sql script is listed below.
SQL> @save_exceptions.sql
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> SELECT COUNT(*)
2 FROM exception_test;
COUNT(*)
----------
98
1 row selected.
SQL> SET ECHO OFF
As expected the test table contains 98 of the 100 records, and the associated error message has been displayed by looping through the SQL%BULK_EXCEPTION collection.
If the SAVE EXCEPTIONS clause is omitted from the FORALL statement, execution of the bulk operation stops at the first exception and the SQL%BULK_EXCEPTIONS collection contains a single record. The no_save_exceptions.sql script demonstrates this behavior.
no_save_exceptions.sql
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, -01400);
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
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;
/
SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
Notice that in addition to the SAVE EXCEPTIONS clause being removed, the no_save_exceptions.sql script now traps a different error number. The output from this script is listed below.
SQL> @no_save_exceptions.sql
Number of failures: 1
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into
("TIM_HALL"."EXCEPTION_TEST"."ID")
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*)
2 FROM exception_test;
COUNT(*)
----------
49
1 row selected.
SQL> SET ECHO OFF
As expected there is only a single error in the SQL%BULK_EXCEPTIONS collection, and there are only 49 records in the test table as the operation has rolled back to the preceding implicit savepoint.
