How ROLLBACK behaves in ORACLE when
FORALL is used?
When
we are using FORALL construct to loop through bulk collect data, and if we run
into un-handled exception, all the DML changes made
during previous iterations will be rolled back but if we handle the
exception correctly, it will Rollback the changes made during last execution
which encountered an error. All other previous execution will not be rolled
back.
For
example, during insertion of 50 records into a table if there is an error in
inserting 49th record the exception is saved and process is
continued to load 50th record. The advantage is it prevents the
records to be rolled back during bulk operations.
Create
table and populate it.
CREATE TABLE TEST
(
Test_ID NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc VARCHAR(15),
New_Desc VARCHAR(15)
);
(
Test_ID NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc VARCHAR(15),
New_Desc VARCHAR(15)
);
INSERT INTO TEST(TEST_ID,TEST_DESC)
SELECT 1, ‘TABLE’ FROM DUAL
UNION ALL
SELECT 2, ‘VIEW’ FROM DUAL
UNION ALL
SELECT 3, ‘PACKAGE BODY’ FROM DUAL
UNION ALL
SELECT 4, ‘FUNCTION’ FROM DUAL
UNION ALL
SELECT 5, ‘PROCEDURE’ FROM DUAL;
SELECT 1, ‘TABLE’ FROM DUAL
UNION ALL
SELECT 2, ‘VIEW’ FROM DUAL
UNION ALL
SELECT 3, ‘PACKAGE BODY’ FROM DUAL
UNION ALL
SELECT 4, ‘FUNCTION’ FROM DUAL
UNION ALL
SELECT 5, ‘PROCEDURE’ FROM DUAL;
COMMIT;
SQL> SELECT * From TEST;
TEST_ID TEST_DESC NEW_DESC
---------- --------------- ---------------
1 TABLE
2 VIEW
3 PACKAGE BODY
4 FUNCTION
5 PROCEDURE
Now
let us run following PL/SQL block and check the end result.
/*
PL/SQL block without any exception*/
DECLARE
DECLARE
TYPE Test_ID IS TABLE OF
TEST.TEST_Id%TYPE;
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
v_Test_ID ga_Test_ID;
v_Test_Desc ga_Test_Desc;
V_Err_count NUMBER;
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
v_Test_ID ga_Test_ID;
v_Test_Desc ga_Test_Desc;
V_Err_count NUMBER;
BEGIN
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO v_Test_ID, v_Test_Desc
FROM Test;
SELECT Test_ID, Test_Desc
BULK COLLECT INTO v_Test_ID, v_Test_Desc
FROM Test;
FORALL i IN
va_test_ID.FIRST..va_test_ID.LAST
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,5,’A’)
WHERE TEST_ID = v_test_ID(i);
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,5,’A’)
WHERE TEST_ID = v_test_ID(i);
END;
/* Commit the changes.*/
COMMIT;
END;
COMMIT;
END;
Upon running above PL/SQL
block, everything will be rolled back since we encountered un-handled exception
on the third row. Changes done by previous executions are also rolled back.
SQL> Select * from Test;
TEST_ID
TEST_DESC
NEW_DESC
---------- ------------------------------
------------------------------
1 TABLE
2 VIEW
3 PACKAGE BODY
4 FUNCTION
5 PROCEDURE
Now
let us add the exception handling code and see the effect.
/*
PL/SQL block with an exception*/
DECLARE
DECLARE
TYPE Test_ID IS TABLE OF
TEST.TEST_Id%TYPE;
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
v_Test_ID ga_Test_ID;
v_Test_Desc ga_Test_Desc;
V_Err_count NUMBER;
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
v_Test_ID ga_Test_ID;
v_Test_Desc ga_Test_Desc;
V_Err_count NUMBER;
BEGIN
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO v_Test_ID, v_Test_Desc
FROM Test;
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO v_Test_ID, v_Test_Desc
FROM Test;
FORALL i IN
va_test_ID.FIRST..va_test_ID.LAST
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,5,’A’)
WHERE TEST_ID = v_test_ID(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
COMMIT;
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,5,’A’)
WHERE TEST_ID = v_test_ID(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
COMMIT;
END;
END;
END;
Make a note of the point that
we haven’t added a line to Rollback the change in exception. If we issue
rollback command in exception block, then all the changes will be rolled back.
That should be the standard practice but it we miss it then we may run into
situation where some records are changed and some not. Let us see the
result.
SQL> Select * from Test;
TEST_ID
TEST_DESC
NEW_DESC
---------- ------------------------------
------------------------------
1
TABLE
TABLEAAAA
2
VIEW VIEWAAAA
3 PACKAGE BODY
4 FUNCTION
5 PROCEDURE
Again, we got an error at 3rd
record, which got rolled back but previous two executions didn’t rollback and
got committed.
Now Let’s try with SAVE
EXCEPTION
/*
PL/SQL block with SAVE EXCEPTION*/
DECLARE
TYPE Test_ID IS
TABLE OF TEST.TEST_Id%TYPE;
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
v_Test_ID Test_ID;
v_Test_Desc
Test_Desc;
v_Error_count
NUMBER;
Ex_Dml_Errors EXCEPTION;
PRAGMA EXCEPTION_INIT(Ex_Dml_Errors, -24381);
BEGIN
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO v_Test_ID, v_Test_Desc
FROM Test;
FORALL i IN v_test_ID.FIRST..v_test_ID.LAST SAVE EXCEPTIONS
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(' ',5,'A')
WHERE TEST_ID = v_test_ID(i);
EXCEPTION
WHEN
Ex_Dml_Errors THEN
v_error_count
:= SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || v_error_count);
FOR i IN 1 ..
v_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;
/* Commit the changes.*/
COMMIT;
END;
Above code will capture the
Error and will show it on screen and continue inserting data.
SQL> DECLARE
2 TYPE Test_ID IS TABLE OF TEST.TEST_Id%TYPE;
3 TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
4 v_Test_ID Test_ID;
5 v_Test_Desc Test_Desc;
6 v_Error_count NUMBER;
7 Ex_Dml_Errors EXCEPTION;
8 PRAGMA EXCEPTION_INIT(Ex_Dml_Errors, -24381);
9 BEGIN
10 BEGIN
11 SELECT Test_ID, Test_Desc
12 BULK COLLECT INTO v_Test_ID, v_Test_Desc
13 FROM Test;
14 FORALL i IN v_test_ID.FIRST..v_test_ID.LAST SAVE EXCEPTIONS
15 UPDATE TEST
16 SET NEW_DESC = TEST_DESC || LPAD(' ',5,'A')
17 WHERE TEST_ID = v_test_ID(i);
18 EXCEPTION
19 WHEN Ex_Dml_Errors THEN
20 v_error_count := SQL%BULK_EXCEPTIONS.count;
21 DBMS_OUTPUT.put_line('Number of failures: ' || v_error_count);
22 FOR i IN 1 .. v_error_count LOOP
23 DBMS_OUTPUT.put_line('Error: ' || i ||
24 ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
25 ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
26 END LOOP;
27 END;
28 /* Commit the changes.*/
29 COMMIT;
30 END;
31 /
Number of failures: 1
Error: 1 Array Index: 3 Message: ORA-12899: value too large for column (actual:
, maximum: )
PL/SQL procedure successfully completed.
As
you can see above Procedure has completed and SAVE EXCEPTION has captured one
Error and displayed the Error message. If you check the Table, you will be
having updated Data in your Table except the discarded record.
SQL> SELECT * From TEST;
TEST_ID TEST_DESC NEW_DESC
---------- --------------- ---------------
1 TABLE TABLEAAAA
2 VIEW VIEWAAAA
3 PACKAGE BODY
4 FUNCTION FUNCTIONAAAA
5 PROCEDURE PROCEDUREAAAA
SAVE
EXCEPTION Another Example
Create
a table to insert BULK Records.
CREATE TABLE Exception_Test
(
ID
NUMBER(10) NOT NULL
);
The following code creates a collection with 30 rows, but sets the
value of rows 11th and 17th 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.
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 .. 30 LOOP
l_tab.Extend;
l_tab(l_tab.last).ID
:= i;
END LOOP;
-- Cause a failure.
l_tab(11).id := NULL;
l_tab(17).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;
END;
SQL> DECLARE
2 TYPE T_tab IS TABLE OF Exception_Test%ROWTYPE;
3 l_tab T_tab := t_tab();
4 l_error_count NUMBER;
5 Ex_Dml_Errors EXCEPTION;
6 PRAGMA EXCEPTION_INIT(Ex_Dml_Errors, -24381);
7 BEGIN
8 -- Fill the collection.
9 FOR i IN 1 .. 30 LOOP
10 l_tab.Extend;
11 l_tab(l_tab.last).ID := i;
12 END LOOP;
13
14 -- Cause a failure.
15 l_tab(11).id := NULL;
16 l_tab(17).id := NULL;
17
18 EXECUTE IMMEDIATE 'TRUNCATE TABLE Exception_Test';
19
20 -- Perform a bulk operation.
21 BEGIN
22 FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
23 INSERT INTO Exception_Test VALUES l_tab(i);
24 EXCEPTION
25 WHEN Ex_Dml_Errors THEN
26 l_error_count := SQL%BULK_EXCEPTIONS.count;
27 DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
28 FOR i IN 1 .. l_error_count LOOP
29 DBMS_OUTPUT.put_line('Error: ' || i ||
30 ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
31 ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
32 END LOOP;
33 END;
34 END;
35 /
Number of failures: 2
Error: 1 Array Index: 11 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 17 Message: ORA-01400: cannot insert NULL into ()
PL/SQL procedure successfully completed.
As expected errors were trapped. If we query the table, we can see that rows were inserted correctly. Check the Data count on Table.
SQL> SELECT Count(*) From EXCEPTION_TEST;
COUNT(*)
----------
28
It's RUDE to
Read and Run!
Get involved
and leave your Comments in the Box Below. The more people get involved, the
more we all benefit.
So, leave your thoughts before you leave the page.
So, leave your thoughts before you leave the page.
Good explanation
ReplyDelete