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.
