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 ...