By Michael O’Neill http://twitter.com/cleverideanet (professional)
http://twitter.com/oraclenude (personal)
oraclenerd articlesIf 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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMvgFQ7mK5v5Ojfjrd_Heh4-gF19LvdANTMo6O6KVKJ8iLcFBlR0MoJjhjDWtHZP5-7Q4q1loapO0heUiEpE32fG-3qjvGKBMq6F2pOeU2hRNyBvrgNyE7ak89WQWo7_UdmUjHJXuqTltZ/s800/01.png")
