Pages

Monday, March 8, 2010

Weird exception handling

PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my conclusion was: the existing procedure and my new one will not be called. Ever. To support my claim, I had built a small script to simulate the situation. But I missed a tiny detail that seemed irrelevant at first, but which appeared to be crucial.

Here's my first simulation of the situation: two packages, one with a global public exception and a second with a local exception with the same name.

rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 procedure start1;
5 end pkg1;
6 /

Package created.

rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /

Package created.

rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /

Package body created.

rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 e_wrong_contents exception;
6 begin
7 raise e_wrong_contents;
8 exception
9 when e_wrong_contents then
10 raise e_wrong_contents;
11 when others then
12 -- do something else
13 null;
14 end local_procedure
15 ;
16 procedure start2
17 is
18 begin
19 local_procedure;
20 exception
21 when pkg1.e_wrong_contents then
22 -- existing_procedure;
23 -- my_new_procedure;
24 dbms_output.put_line('pkg1.e_wrong_contents was raised');
25 when others then
26 dbms_output.put_line('others');
27 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
28 end start2
29 ;
30 end pkg2;
31 /

Package body created.

My claim was that the existing procedure and my new one were never invoked, because pkg1.e_wrong_contents is never raised. Only the local one, but that one loses scope in procedure start2 and becomes a user defined exception there, as can be seen by running pkg1.start1:

rwijk@ORA11GR1> exec pkg1.start1
others
User-Defined Exception
ORA-06512: at "RWIJK.PKG2", line 10
ORA-06512: at "RWIJK.PKG2", line 16


PL/SQL procedure successfully completed.


The point is that pkg1.e_wrong_contents and e_wrong_contents may look related, but they are not.

After having witnessed that the existing procedure does get invoked in the production database, we had a second look at my script and a colleague pointed out that I missed one detail: a pragma exception_init to a user defined error number (-20660). I had left it out on purpose, since there is no point in assigning a user defined exception to user error number that is not used. I had checked there wasn't any RAISE_APPLICATION_ERROR(-20660,...) in the entire schema. With the pragma exception_init added, the script looks like this:

rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 pragma exception_init(e_wrong_contents,-20660)
5 ;
6 procedure start1;
7 end pkg1;
8 /

Package created.

rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /

Package created.

rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /

Package body created.

rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 e_wrong_contents exception;
6 pragma exception_init(e_wrong_contents,-20660);
7 begin
8 raise e_wrong_contents;
9 exception
10 when e_wrong_contents then
11 raise e_wrong_contents;
12 when others then
13 -- do something else
14 null;
15 end local_procedure
16 ;
17 procedure start2
18 is
19 begin
20 local_procedure;
21 exception
22 when pkg1.e_wrong_contents then
23 -- existing_procedure;
24 -- my_new_procedure;
25 dbms_output.put_line('pkg1.e_wrong_contents was raised');
26 when others then
27 dbms_output.put_line('others');
28 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
29 end start2
30 ;
31 end pkg2;
32 /

Package body created.

And executing pkg1.start1:

rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised

PL/SQL procedure successfully completed.

Now the code is invoked and that's how it works in production. The pragma exception_init assigns both the global and the local one to ORA-20660, which makes them equal.

Why you would ever want to code it like this, I don't know. It's probably best categorized as "historically grown like that".

Defining your own exception, assigning it to a user error number and then raise your exception, is equivalent to just doing a raise_application_error:

rwijk@ORA11GR1> declare
2 e_wrong_contents exception;
3 pragma exception_init(e_wrong_contents,-20660);
4 begin
5 raise e_wrong_contents;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 5


rwijk@ORA11GR1> begin
2 raise_application_error(-20660,null);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 2


And if I get to refactor the code, I would skip the pragma exception_init's and skip the local exceptions. The code would then look like this:

rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 procedure start1;
5 end pkg1;
6 /

Package created.

rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /

Package created.

rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /

Package body created.

rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 begin
6 raise pkg1.e_wrong_contents;
7 end local_procedure
8 ;
9 procedure start2
10 is
11 begin
12 local_procedure;
13 exception
14 when pkg1.e_wrong_contents then
15 -- existing_procedure;
16 -- my_new_procedure;
17 dbms_output.put_line('pkg1.e_wrong_contents was raised');
18 when others then
19 dbms_output.put_line('others');
20 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
21 end start2
22 ;
23 end pkg2;
24 /

Package body created.

rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised

PL/SQL procedure successfully completed.

Simulating production situations using your own little hand written script is a valuable technique, but you have to be careful to include all the necessary details ...

No comments:

Post a Comment