Never Use RAISE_APPLICATION_ERROR Again

By Michael O’Neill
http://twitter.com/cleverideanet (professional)
http://twitter.com/oraclenude (personal)
oraclenerd articles

If you write Oracle PL/SQL, you know what RAISE_APPLICATION_ERROR is. It is an abomination of hard-coding and poor practice. If you didn't know that, I'm sorry I was the one who told you. I've written and used extensively an ultra-simple framework to eliminate RAISE_APPLICATION_ERROR from my code forever.
Here's an example (assume 11gR2) of something we all know we can do:

begin  dbms_output.put_line(1/0); end;

This will throw an unhandled ORA-01476 exception. We could write some meaningful handling of that with this:

begin 
   dbms_output.put_line(1/0);
exception
   when zero_divide
   then
      dbms_output.put_line('zero divide exception caught');
end;

This coding is elegant because Oracle has conveniently predefined an exception named ZERO_DIVIDE and a corresponding pragma for us. Unfortunately, Oracle has only 22 predefined exceptions. What happens when I do this:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
end;

This will throw an unhandled ORA-01861 exception. My option to handle this is less than meaningful because this is not a predefined exception:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when others
   then
      case sqlcode
         when -1861
         then
            dbms_output.put_line('literal does not match exception caught');
         else
            raise;
      end case;
end;

This leads me to the inevitable desire to create my own named exception and pragma, so I could have code that looks like this instead:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when error.ora_literal_string_mismatch
   then
      dbms_output.put_line('literal does not match exception caught');
end;

Understanding this, creating my own ERROR package with a friendly named exception and pragma for ORA-01861 leads me to the pattern of how to handle my own application exceptions, namely defining an exception and pragma.

But how does this get RAISE_APPLICATION_ERROR out of my life? Consider the ERROR abbreviated package source I use (full source: error.pks and error.pkb):

create or replace package error is
 
   package_name constant varchar2(32) := 'error'; -- in case you want to change the package name
 
   -- application exceptions and pragmas
 
   (snip)
 
   not_one_based constant string(64) := package_name || '.app_not_one_based';
   app_not_one_based exception;
   pragma exception_init(app_not_one_based, -20004);
 
   sparsity_not_allowed constant string(64) := package_name || '.app_sparsity_not_allowed';
   app_sparsity_not_allowed exception;
   pragma exception_init(app_sparsity_not_allowed, -20003);
 
   parameter_cannot_be_null constant string(64) := package_name || '.app_parameter_cannot_be_null';
   app_parameter_cannot_be_null exception;
   pragma exception_init(app_parameter_cannot_be_null, -20002);
 
   string_too_large constant string(64) := package_name || '.app_string_too_large';
   app_string_too_large exception;
   pragma exception_init(app_string_too_large, -20001);
 
   application_exception constant string(64) := package_name || '.app_application_exception';
   app_application_exception exception;
   pragma exception_init(app_application_exception, -20000);
 
   -- rdbms exceptions and pragmas
 
   (snip)
 
   literal_string_mismatch constant string(64) := package_name || '.ora_literal_string_mismatch';
   ora_literal_string_mismatch exception;
   pragma exception_init(ora_literal_string_mismatch, -1861);
 
   (snip)
 
   procedure throw(p_exception in varchar2);
 
   procedure throw
   (
      p_exception in varchar2
     ,p_message   in varchar2
   );
 
end;

You can see several user-defined exceptions and pragmas as well as the ORA_LITERAL_STRING_MISMATCH used in the previous example. The full source has more defined, but is not relevant to understanding the concept I am presenting.

Notice there is just one (overloaded) method, THROW. THROW is what I use instead of RAISE_APPLICATION_ERROR.

So, instead of this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      raise_application_error(-20000, 'I hate the letter b');
   end if;
end;

I use this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      error.throw(error.application_exception, 'I hate the letter b');
   end if;
end;

On its surface this doesn’t seem terribly interesting or useful. Below the surface, several powerful advantages are gained:

• A single ERROR package encapsulates a schema’s application exceptions and pragmas, giving me a consistent SQLCODEs returned to my C# code.
• No more, remembering what number to use in RAISE_APPLICATION_ERROR.
• Easier to understand code
• I can effectively organize my exceptions without sprawling them throughout a schema’s packages
• I can extend the ERROR package (and I have) to do many more things like logging or default messages for exceptions without writing that into my schema’s application packages.

How does it work? Taking a look at the body for the THROW method reveals all:

procedure throw
(
   p_exception in varchar2
  ,p_message   in varchar2
) is
begin
   begin
      begin
         execute immediate ('begin raise ' || p_exception || '; end;');
         -- exception is raised and immediately trapped
      exception
         when ora_plsql_compilation_error then
            throw(error.exception_does_not_exist, p_exception);
      end;
     
   exception
      when others then
         if sqlcode between - 20999 and - 20000
         then
            raise_application_error(sqlcode, p_message);
            -- this is the best/only use of raise_application_error
            -- and eliminates the need in application code
         else
            raise;
            -- nothing extra to do for extra for exceptions outside raise_application_error range
         end if;
   end;
exception
   when others then
      raise; -- finally, bubbles up the original throw() call
end throw;

There are few caveats I have using this development pattern. I don’t consolidate every exception I write into my ERROR package, only those exceptions that I want to bubble up unhandled to my C# code. I don’t feel it is necessary to have the same ERROR package in every application schema. In other words I don’t evolve every incarnation of my ERROR package when I’m adding exceptions and pragmas to one schema’s ERROR package. Finally, my ERROR package has a multitude more bells and whistles I’m not sharing in this post for clarity’s sake. If you are interested in a more extended version of my ERROR, let me know via Twitter (@cleverideanet)

Copyright © 2010 Michael O'Neill
Published by Permission on oraclenerd