 |
|
Oracle Tips by Burleson |
Defining and Raising Exceptions
Exceptions can be defined in the declaration
section of any PL/SQL block. In fact, the exceptions
discussed so far are Oracle-defined exceptions. By defining your own
exceptions you can use the exception handling capability of PL/SQL to
either act on errors, or terminate processing and return to the
calling block. Below, two exceptions are defined in the declaration
section of an anonymous PL/SQL block.
declare
creditcard_no_good exception;
creditcard_overlimit exception;
begin
-- customer places items in cart
if (check_out) then
begin
get_customer_data(n_custNum, ... );
if (creditcard) then
begin
if (invalid) then raise
creditcard_no_good;
if (overLimit) then raise
creditcard_overlimit;
end;
exception
when creditcard_no_good then ...
when creditcard_overlimit then ...
when others then raise big_problem;
end;
... more processing
end;
Two exceptions are defined in the declaration
section of the above code fragment. During customer checkout, the
customer credit card is verified and if it fails, the exceptions are
raised, and the exception handler catches both exceptions and handles
them. Any other exception is handled by the OTHERS clausewhich itself raised a BIG_PROBLEM exception (handled somewhere else
in the code). The code can also raise an Oracle defined exception.
Below, the function catches the CASE_NOT_FOUNDexception and raises the ZERO_DIVIDE exception.
SQL> create
or replace function bad_convert
2 (n_number IN number)
3 return varchar2
4 as
5 begin
6 case n_number
7 when 1 then return 'one';
8 when 2 then return 'two';
9 when 3 then raise ZERO_DIVIDE;
10 end case;
11 exception
12 when CASE_NOT_FOUND
13 then return 'Bad Conversion';
14 end;
15 /
Function
created.
Programmer defined exceptions should be named so
that the user (or maintainer) understands what raised the exception.
The BIG_PROBLEM exception is an example of a poorly named exception.
It is not very informative to have your application fail with a
BIG_PROBLEM exception because it is ambiguous. Oracle provides the
RAISE_APPLICATION_ERRORprocedure to allow the
programmer to provide additional information to an exception
definition when the exception is raised.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
