Handling Exceptions in Bulk Operations
The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:
There are a number of issues regarding exception handling that must be considered when using bulk operations. In this section, rollback behavior of bulk operations and the methods available to control this behavior is examined.
In order to demonstrate this functionality, a simple test table containing a single mandatory column must first be created. This is performed using the exception_test.sql script listed below.
exception_test.sql
CREATE TABLE exception_test (
id NUMBER(10) NOT NULL
);
id NUMBER(10) NOT NULL
);
After the table is created, the way unhandled exceptions are treated during bulk operations can be examined.
Unhandled Exceptions
Unhandled exceptions during the execution of a bulk operation cause the entire operation to be rolled back. This functionality is demonstrated using the unhandled_exception.sql script listed below.
unhandled_exception.sql
DECLARE
TYPE t_tab IS TABLE OF exception_test.id%TYPE;
TYPE t_tab IS TABLE OF exception_test.id%TYPE;
l_tab t_tab := t_tab();
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last) := i;
END LOOP;
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last) := i;
END LOOP;
-- Cause a failure.
l_tab(50) := NULL;
l_tab(50) := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
-- Perform a bulk operation.
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO exception_test
VALUES (l_tab(i));
END;
/
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO exception_test
VALUES (l_tab(i));
END;
/
SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
The unhandled_exception.sql script first creates and populates a collection. Next it assigns the value of NULL to the 50th element, thereby forcing an error. It then truncates the test table, attempts a bulk insert against it and displays the record count. The output from this script is listed below.
SQL> @unhandled_exception.sql
DECLARE
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")
ORA-06512: at line 18
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")
ORA-06512: at line 18
SQL> SELECT COUNT(*)
2 FROM exception_test;
2 FROM exception_test;
COUNT(*)
----------
0
1 row selected.
SQL> SET ECHO OFF
As expected an exception is raised when the bulk operation reaches the 50th element, resulting in the whole operation being rolled back. The rollback is evident since the record count is zero.
The following section shows the way handled exceptions are treated during bulk operations.
Handled Exceptions
During a bulk operation a savepoint is created between each SQL execution. In the event a handled exception is raised, the operation is rolled back to the previous savepoint instead of restarting the whole operation. The handled_exception.sql script listed below demonstrates this behavior.
handled_exception.sql
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF exception_test.id%TYPE;
DECLARE
TYPE t_tab IS TABLE OF exception_test.id%TYPE;
l_tab t_tab := t_tab();
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last) := i;
END LOOP;
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last) := i;
END LOOP;
-- Cause a failure.
l_tab(50) := NULL;
l_tab(50) := 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 OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
END;
/
BEGIN
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO exception_test
VALUES (l_tab(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
END;
/
SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF
The handled_exception.sql script is a modified version of the unhandled_exception.sql script in that the bulk operation has been surrounded by an anonymous block containing an exception handler that displays the error message. The output from this script is listed below.
SQL> @handled_exception.sql
ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*)
2 FROM exception_test;
2 FROM exception_test;
COUNT(*)
----------
49
----------
49
1 row selected.
SQL> SET ECHO OFF
Once again the bulk operation results in an exception, but this time the exception is trapped so the rollback is restricted to the previous savepoint, as shown by the record count of 49.
That works well if the goal is to stop the operation at that point, but what if the goal is to proceed past any problem rows? In order to achieve this, the SAVE EXCEPTIONS clause of the FORALL statement must be used; the subject of the next section.
Bulk Operations that Complete
Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing. Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute. This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception. In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.
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;
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
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;
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;
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;
/
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
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 ()
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;
2 FROM exception_test;
COUNT(*)
----------
98
----------
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;
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
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;
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;
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;
/
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
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")
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;
2 FROM exception_test;
COUNT(*)
----------
49
----------
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.
As shown from previous examples, a move from conventional operations to bulk operations will require a revision of your current exception handling or the desired results may not appear.
The use of bulk operations with dynamic SQL is explained in the next section.
No comments:
Post a Comment