 |
|
EnterpriseDB: Exception Handling
Oracle Tips by
Burleson
|
Exceptions
SPL has
very robust exception handling. An exception handler takes care of
"expected" errors and can be built to intelligently handle unexpected
exceptions.
As I
have discussed above, the exception area is one of the areas in a SPL
block. The syntax for the EXCEPTION area is:
EXCEPTION
WHEN <exception> THEN
<executable statements>;
[WHEN <exception> THEN
<executable statements>;]
[WHEN OTHERS THEN
<executable statements>;
The
EXCEPTION keyword identifies the exception area. The EXCEPTION
keyword is followed by one or more WHEN clauses. The WHEN clause
identifies the named exception that you are handling, i.e.
NO_DATA_FOUND, or the OTHERS
clause.
The OTHERS will fire for any exceptions.
The WHEN
clauses fire in the order written and firing will stop at the first
WHEN clause that matches the current exception. That means that if
you are going to include an OTHERS handler, it will always be the
final WHEN clause.
The
easiest way to think about exceptions is to think of them as
unfavorable interruptions in program flow control. The exceptions may
or may not be an expected change in your program flow, but an
exception is always (mostly) unfavorable. That's the easiest way but,
of course, every rule has an exception. It would not be an error to
say that exceptions are no exception to that rule.
You will
certainly use exceptions to handle errors in your program. No data
when you expect it, too many rows returned from a select statement,
user defined errors, etc. An exception may be completely expected
though.
In
certain cases, not finding data is the expected result of a query and
you have the choice of using an explicit or implicit cursor. Implicit
cursors are easier to maintain and the way you would "check" for no
rows would be to use a no data found exception handler.
I think
it's important for me to say here that I have seen a lot of very good
code and a lot of very bad code. SPL is a very powerful language.
Exceptions are a very powerful feature of that language. DO NOT use
exceptions in place of GOTO. If you do, you will
create
an unmaintainable mess.
* Do NOT
use exceptions as a cheap GOTO. GOTO is bad for a
reason.
Exceptions used that way are just as bad.
Exceptions are there to allow you to gracefully handle exception
conditions in your logic. Use them that way. A good rule of thumb
when creating your exception handler is to consider if you could
replace the exception with a GOTO and a label. If you can, you are
probably using the exception handler inappropriately.
Repeat
after me: exceptions are not flow control. Exceptions should not be
used for flow control. They are to handle exceptions (and may change
the flow) but they are not intended to manage flow.
There
are two kinds of exceptions you will use in SPL: Named Exceptions and
User Defined Exceptions. Named exceptions are part of the language
and you cannot change them. User defined exceptions are created with
the built-in procedure, RAISE_APPLICATION_ERROR.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
