Monday, October 4, 2010

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

7 comments:

Anonymous said...

Watch out for SQL injection with a malformed p_exception argument passed to the dynamic SQL in the throw procedure. For example...

begin
error.throw
(
'program_error; exception when others then execute immediate ''drop table t''',
null
);
end;

...might have some nasty results.

Some other observations:

1. Is the outermost block necessary, since it just re-raises all exceptions anyway?

2. The "message" does not get raised for exceptions passed to throw() that are outside the RAISE_APPLICATION_ERROR range. I don't know of any documented way around this (that is, to raise an exception outside the RAISE_APPLICATION_ERROR range with a custom message).

3. Do you even need to use dynamic SQL? Suppose you used number constants instead of string constants to identify your exceptions and passed these to throw(). For exceptions within the RAISE_APPLICATION_ERROR range, throw() could use RAISE_APPLICATION_ERROR (using the number passed) and the caller could use the WHEN syntax (using one of the pre-initialized exceptions) as illustrated below:

SQL> set serveroutput on
SQL> declare
2 app_application_exception exception;
3 pragma exception_init(app_application_exception, -20000);
4 begin
5 raise_application_error(-20000, 'Hello, world!');
6 exception
7 when app_application_exception then
8 dbms_output.put_line('Exception caught');
9 end;
10 /
Exception caught

For exceptions outside the RAISE_APPLICATION_ERROR range, could you simply remove support for these from the throw() procedure? Instead, code wanting to throw these exceptions could use a standard RAISE with one of the pre-initialized (or predefined) exceptions.

DomBrooks said...

I don't think we need to worry about people using SQL injections against this error package.

We're talking about developers using the error package.

If they could just as easily write " execute immediate 'drop table t' " as inject it..


On the subject of the article, yes it can be nice to package up all these errors with consistent codes and messages etc but most of the time it's not a big deal, and I certainly don't feel the same way about raise_application_error as the author.

But nicely put together.

Clever Idea Widgetry said...

Reply to Anonymous:

I agree with the potential risk of SQL injection, however this has been been code written by myself for myself primarily for data access layer programming. There's no public end-user interface of the throw. Still, I ought to use a regular expression check to mitigate that risk.

Yes the outermost block is necessary for one not obvious reason: That's were my hook for an autonomously committed logging feature exists, before the final RAISE.

The use of dynamic SQL is convenience. Long ago in the evolution of my ERROR package I had more concrete calls to THROW with constants - but it was more work than I liked to maintain.

Clever Idea Widgetry said...

In reply to DomBrooks:

The biggest reason I dislike the use of RAISE_APPLICATION_ERROR is how inconsistently it presents itself to my C# code.

Back in the day, when all I did was PL/SQL coding that referenced other PL/SQL code I just used named exceptions (with no pragma) or just threw in quick and dirty RAISE_APPLICATION_ERROR(-20000, msg) every time and moved on with my life.

C# changed that for me. As a C# developer, it's important to me to get consistent SQLCODEs and messages from the PL/SQL code.

Anonymous said...

As a developer of an extensive system with lots of packages and dependencies, I would like to see how you add just one new error to that package and then recompile all references...

In a big system, either you use something everywhere OR you change it frequently - never both.

Sakamoto said...

Hi Michael,

I read your article and would like to see the full code, could send me email mytracelog@gmail.com?

MyTracelog - Registro de um DBA
http://mytracelog.blogspot.com

oraclenerd said...

@Sakamoto,

The code is up there, search for "error.pks" and "error.pkb" to find it. It's stored in a public Google Drive folder.

chet