Whilst deploying a data fix (written by someone else) yesterday the SQL script that I was running hit an error midway through a PL/SQL anonymous block. A simplificiaton of the script is show below:
- Create table A
- Insert 2 records into table A
- Commit
- Run anonymous block:
- Cursor from table A to update table B, C & D
- Insert into E
- Commit
- Commit
- Drop table A
There are a few things wrong with how it was written, but that’s not the point I’m interested in and might not be obvious without the actual code.
When running the script the insert into E failed and I was left with output showing table A was created successfully and populated, the PL/SQL block failed, the commit outside the PL/SQL block was successful and table A was dropped…
The question for me was what had happened to the updates to B, C and D? There was no exception handler in the anonymous block so I was confident that the commit in the block had never been reached, but would the commit outside of the block have committed the updates to B, C & D for the first record of the cursor? After a quick chat with a colleague we concluded that the updates within the block will have been neither committed nor rolled back in the PL/SQL and then committed by the commit immediately after the block… It was getting late and I had plenty of other things to do so I sent the output to the developer so he could provide a new script.
This morning I got a call from the developer and he explained that he’d forgotten to comment out the insert in E, as you do! He went on to ask if I’d rolled back the change as neither of the two sets of updates had happened (there were 2 records created in table A that formed the cursor).
This pointed to my colleague and I getting it wrong so it was time to prepare a quick test and that’s what follows…
I created a table with 10 rows as shown.
create table a(a number);
insert into a select rownum from all_objects
where rownum < 11;
commit;
Next I created a script “pl_test_01.sql”:
-- Start of pl_test_01.sql --
update a set a = a + 10 where a = 1;
begin
update a set a = a + 10 where a = 2;
update a set a = a + 10 where a = 3;
insert into b (b) values (100);
commit;
end;
/
update a set a = a + 10 where a = 4;
commit;
-- End of pl_test_01.sql --
* Note that table B did not exist.
I then ran the following commands:
SQL> select * from a;
A
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> @pl_test_01.sql
1 row updated.
insert into b (b) values (100);
*
ERROR at line 4:
ORA-06550: line 4, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
1 row updated.
Commit complete.
SQL> select * from a;
A
----------
11
2
3
14
5
6
7
8
9
10
10 rows selected.
SQL>
“OK.”, I thought, “Well that explains what the developer reported.” But, what is initiating the rollback? This was undermining what the two of us believed about how PL/SQL handles transactions.
After a bit of playing around with commits in different places and considering various options, including the type of error I was generating I ran “pl_test_05.sql”
-- Start of pl_test_05.sql --
update a set a = a + 10 where a = 1;
declare
l_var number := 0;
begin
update a set a = a + 10 where a = 2;
update a set a = a + 10 where a = 3;
insert into a (a) values (100/l_var);
exception
  when others then
  null;
end;
/
update a set a = a + 10 where a = 4;
commit;
-- End of pl_test_05.sql --
“When others then null” – Tom Kyte would have a fit! I was working on the basis that this was testing of functionality and was therefore acceptable.
SQL> select * from a;
        A
----------
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
10 rows selected.
SQL> @pl_test_05.sql
1 row updated.
PL/SQL procedure successfully completed.
1 row updated.
Commit complete.
SQL> select * from a;
        A
----------
       11
       12
       13
       14
        5
        6
        7
        8
        9
       10
10 rows selected.
SQL>
Bingo! I’d managed to create the opposite behaviour of what I’d initially seen. SQL*Plus was rolling back the unhandled error.
After a bit of reading around I came across the following on page 23 of “Oracle PL/SQL for DBAs” (Arup Nanda and Steven Feuerstein):
That environment (a tool like SQL*Plus, Oracle Forms, or a Java program) then takes an action appropriate to the situation; in the case of SQL*Plus, a ROLLBACK of any DML changes from within the top-level block’s logic is automatically performed.
I’ve ended another day feeling like I’ve learn something new.
Added 11/03/2009
… Or maybe not! A colleage pointed me towards chapter 4 – Transactions of Expert One-on-One Oracle by Tom Kyte, where there is a different explanantion. According to Tom this isn’t a case of SQL*Plus rolling back the transaction, but actually the result of:
It is interesting to note that Oracle considers PL/SQL anonymous blocks to be statements
as well.
ORAganism 