Systems Engineering and RDBMS

Back to the Basics: ROLLBACK behavior when FORALL is used

Posted by decipherinfosys on March 12, 2009

In our previous blog post, we covered how can we handle multiple exception when using bulk collect. Today we will show you how FORALL construct used in bulk collect effects the rollback behavior.  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. Our example is based on one shown in Oracle manual. Let us first create table and populate it.

CREATE TABLE TEST
(
Test_ID     NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc   VARCHAR(30),
New_Desc    VARCHAR(30)
);

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;

COMMIT;

Now let us run following PL/SQL block and check the end result.

/* PL/SQL block without any exception*/
DECLARE

TYPE ga_Test_ID   IS TABLE OF TEST.TEST_Id%TYPE;
TYPE ga_Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
va_Test_ID    ga_Test_ID;
va_Test_Desc  ga_Test_Desc;
V_Err_count   NUMBER;

BEGIN

BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO va_Test_ID, va_Test_Desc
FROM Test;

FORALL i IN va_test_ID.FIRST..va_test_ID.LAST
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,22,’A’)
WHERE TEST_ID = va_test_ID(i);
END;

/* Commit the changes.*/
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 two 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

TYPE ga_Test_ID   IS TABLE OF TEST.TEST_Id%TYPE;
TYPE ga_Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
va_Test_ID    ga_Test_ID;
va_Test_Desc  ga_Test_Desc;
V_Err_count   NUMBER;

BEGIN
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO va_Test_ID, va_Test_Desc
FROM Test;

FORALL i IN va_test_ID.FIRST..va_test_ID.LAST
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,22,’A’)
WHERE TEST_ID = va_test_ID(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
COMMIT;
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                          TABLEAAAAAAAAAAAAAAAAAAAAA
2 VIEW                           VIEWAAAAAAAAAAAAAAAAAAAAA
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.

Resources:

PL/SQL Reference Manual – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: