Posted by This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.
Lets set the scene with a simple procedure that commences a transaction, but then always fails
SQL> drop table test$tab purge;
Table dropped.
SQL>
SQL> create table test$tab (val varchar2(1));
Table created.
SQL>
SQL> create or replace procedure foo_proc is
2 begin
3 dbms_output.put_line('in foo_proc');
4 insert into test$tab values ('t');
5 raise_application_error(-20001,'Error');
6 end;
7 /
Procedure created.
One of the cool things about PL/SQL is its nice handling of transaction processing, or often described with a statement along the lines of: “A PL/SQL procedure acts as a single unit of work”.
We can see a simple demonstration of this by running the procedure we just created
SQL> exec foo_proc
in foo_proc
BEGIN foo_proc; END;
*
ERROR at line 1:
ORA-20001: Error
ORA-06512: at "MCDONAC.FOO_PROC", line 5
ORA-06512: at line 1
SQL> select * from test$tab;
no rows selected
and as expected, the insertion was rolled back, to an “unseen” savepoint before the call to the procedure.
Simple enough eh ? Well no…there is more to the picture here. Let’s now call that procedure in some different scenarios (colour coding mine)
- From an anonymous block
- Using native dynamical SQL
- Using DBMS_SQL
SQL> set serverout on
SQL> declare
2 stmt varchar2(200);
3 c number;
4 i number;
5
6 function count_rows return number is
7 l_cnt number;
8 begin
9 select count(*) into l_cnt from test$tab t;
10
11 return l_cnt;
12 end;
13
14 begin
15
16 delete from test$tab;
17 begin
18 foo_proc;
19 exception
20 when others then
21 dbms_output.put_line('static call - ' || count_rows);
22 end;
23
24 delete from test$tab;
25 begin
26 execute immediate 'begin foo_proc; end;';
27 exception
28 when others then
29 dbms_output.put_line('nds - ' || count_rows);
30 end;
31
32 delete from test$tab;
33 begin
34 stmt := 'begin foo_proc; end;';
35
36 i := dbms_sql.open_cursor;
37 dbms_sql.parse(c => i, statement => stmt, language_flag => dbms_sql.native);
38 c := dbms_sql.execute(i);
39 dbms_sql.close_cursor(i);
40 exception
41 when others then
42 dbms_output.put_line('dbms_sql - ' || count_rows);
43 if dbms_sql.is_open(i)
44 then
45 dbms_sql.close_cursor(i);
46 end if;
47 end;
48 end;
49 /
in foo_proc
static call - 1
in foo_proc
nds - 0
in foo_proc
dbms_sql - 0
PL/SQL procedure successfully completed.
The results appear to be inconsistent, but they are not. This is working as designed, and is documented in the PLSQL manual.
From http://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS850
“If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram.”
So that takes care of the first test above – we called a subprogram, we did the change and did not roll it back. So what of the last two cases ?
These are just examples of standard statement level atomicity, ie, a statement works in its entirety or it doesn’t. In each case, we have executed a single “SQL” statement, which failed, so we roll back the statement.
And just to clarify further, if we remove the exception handler from test case #1, ie, so that the error is returned to the calling environment, then it is the anonymous block itself that become the single statement, and hence the change is rolled back
SQL> set serverout on
SQL> declare
2 stmt varchar2(200);
3 c number;
4 i number;
5
6 function count_rows return number is
7 l_cnt number;
8 begin
9 select count(*) into l_cnt from test$tab t;
10
11 return l_cnt;
12 end;
13
14 begin
15
16 delete from test$tab;
17 commit;
18 begin
19 foo_proc;
20 -- exception
21 -- when others then
22 -- dbms_output.put_line('static call - ' || count_rows);
23 end;
24
25 delete from test$tab;
26 commit;
27 begin
28 execute immediate 'begin foo_proc; end;';
29 exception
30 when others then
31 dbms_output.put_line('nds - ' || count_rows);
32 end;
33
34 delete from test$tab;
35 commit;
36 begin
37 stmt := 'begin foo_proc; end;';
38
39 i := dbms_sql.open_cursor;
40 dbms_sql.parse(c => i, statement => stmt, language_flag => dbms_sql.native);
41 c := dbms_sql.execute(i);
42 dbms_sql.close_cursor(i);
43 exception
44 when others then
45 dbms_output.put_line('dbms_sql - ' || count_rows);
46 if dbms_sql.is_open(i)
47 then
48 dbms_sql.close_cursor(i);
49 end if;
50 end;
51 end;
52 /
in foo_proc
declare
*
ERROR at line 1:
ORA-20001: Error
ORA-06512: at "MCDONAC.FOO_PROC", line 5
ORA-06512: at line 19
SQL>
SQL> select count(*) from test$tab t;
COUNT(*)
----------
0
1 row selected.
Learning is not a spectator sport 
IMHO, foo_proc() should be made “atomic” (ie the “A” in ACID) by having an exception handler do a rollback to an explicit savepoint in the function, so the caller doesn’t have to worry about side effects.
The function can reraise the exception so it can be handled by the caller.
I should probably add “consistent”. That’s the “C” in “ACID”.