|
|
|
|
Handle PL/SQL exceptions with best practices.
Many programmers don't take the time to properly bulletproof their programs. Most of us find it hard enough—and more than enough work—to simply write the code that implements the positive aspects of an application: maintaining customers, generating invoices, and so on.
You write applications that often
assume the best of all possible worlds, hoping that your programs are bug-free,
that users will enter only the correct data in only the correct fashion, and
that all systems (hardware and software) will always be a "go."
No matter how hard you try, there will always be one more bug in your
application. And your users will always find just the right sequence of
keystrokes it takes to make a screen implode. The reality is clear: Either you
spend the time up front to properly debug and bulletproof your programs, or you
will fight an unending series of rear-guard battles, taking frantic calls from
your users and putting out the fires.
Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors
in your programs. It is entirely feasible within the PL/SQL language to build an
application that fully protects the user and the database from errors. And if
you go about it correctly, you minimize the amount of code you need to write to
get the job done.
This article, the first of two parts, will explore a set of best practices you
should follow to incorporate high-quality, comprehensive error handling in your
PL/SQL-based applications. This article will start by considering some overall
best practices for error handling and then focus on best practices relating to
raising exceptions. The next article will move on to how best to handle
exceptions.
Set Guidelines Before You Start
Before you start writing any application code, you first need to decide on your
overall strategy for error handling, as well as specific coding guidelines.
There is no single strategy that works for all applications; you have to decide
what makes the most sense for your particular system. You may determine, for
example, that all stored procedures should have an exception section so that
errors are trapped and handled as close as possible to the block in which they
were raised. You might, on the other hand, decide that you want an exception
section only at the outer-most block.
Once you have set the overall approach, you need to face up to the challenge of
getting all developers on the team to write their error handling in the same
way. If you do not follow consistent approaches to raising, handling, and
logging errors, users will be terribly confused, and people supporting the
application will have a very tough time figuring out what is going wrong.
The best way to implement consistent, application wide error handling is to
build and use a standardized package that contains at least the following
elements:
A raise program that hides the complexity of RAISE_APPLICATION_ERROR and application-specific error numbers
Procedures that perform most exception-handling tasks, such as writing to an error log and handling errors
A function that returns error message text for a given error number
Here is the specification of a simple error-handling package that conforms to the above requirements (and is located in the errpkg.pkg file):
CREATE OR REPLACE PACKAGE errpkg
IS
PROCEDURE raise (
err_in IN INTEGER := SQLCODE,
msg_in IN VARCHAR2 := NULL);
PROCEDURE report_and_stop (
err_in IN INTEGER := SQLCODE,
msg_in IN VARCHAR2 := NULL);
PROCEDURE report_and_go (
err_in IN INTEGER := SQLCODE,
msg_in IN VARCHAR2 := NULL);
FUNCTION errtext (
err_in IN INTEGER := SQLCODE)
RETURN VARCHAR2;
END errpkg;
I will take a look at how to
implement the procedures and functions of this error-handling package (errpkg.pkg),
and the impact they have on your code.
Raising Exceptions
Exceptions can't be handled until they are raised, so I'll begin by looking at
some best practices for raising exceptions. The following best practices
describe how to check for conditions that might require the raising of an
exception, how to decide what exception information to propagate, and how best
to raise exceptions.
Validate Preconditions
Every time you write a program, you make assumptions. Users of your program
don't necessarily know about those assumptions. If you do not "code defensively"
and make sure that your assumptions are not violated, your programs can break
down in unpredictable ways.
Use assertion routines to make it easy to validate assumptions in a declarative
fashion. These routines, standardized for an entire application, take care of
all the housekeeping. They describe what to do when a condition fails, how to
report the problem, and whether and how to stop the program from continuing.
Listing 1 contains a simple assertion program that checks to see if a condition
is TRUE. If the condition is instead FALSE or NULL, the procedure displays a
message to the screen and then optionally raises an exception with dynamic
PL/SQL.
On lines 2 through 6 of Listing 1, I create a parameter list that allows for
significant flexibility, a key aspect of reusable code. First, I supply the
condition that I wish to assert. If the condition (a Boolean expression)
evaluates to TRUE, then the assertion program does nothing. If the expression
evaluates to FALSE or NULL, the assertion procedure then relies on the other
parameters to display the error. I further specify that I want an exception
raised (when raise_exception_in is TRUE, the default). The procedure will use
native dynamic SQL (lines 18 through 19) to raise whatever exception I name in
the fourth parameter (lines 5 through 6).
With the assertion procedure in place, I can in a declarative fashion make sure
that all inputs are valid before proceeding with my business logic. For example:
BEGIN
assert (isbn_in IS NOT NULL,
'The ISBN must be provided.');
assert (page_count_in < 2000,
'Readers don't like big, fat books!');
Try setting up a block template that breaks up your executable section into a number of "virtual" sections, as follows:
CREATE OR REPLACE PROCEDURE <name>
IS
<declarations>
PROCEDURE initialize IS
BEGIN
<any startup code>
END;
PROCEDURE assert IS
BEGIN
<sequence of assertions>
END;
PROCEDURE cleanup IS
BEGIN
<any clean-up code>
END;
BEGIN
initialize;
assert;
<body of code>
cleanup;
END <name>;
Prototypes for assertion procedures
like those shown here are located in the assert.pro and assert.pkg files.
Use the Default Model
If you are working with a programming language that does not have a
sophisticated error-handling architecture (unlike PL/SQL or Java), you may have
gotten used to adding OUT parameters to every program that passes back status
code and messages.
Don't do this in PL/SQL, unless you need to pass back such information to the
host environment that is calling the PL/SQL code. If your PL/SQL programs are
communicating with and being called from other PL/SQL blocks, you should rely on
the default model: raise exceptions and handle those exceptions in separate
exception sections of your blocks.
Here is an example of code to avoid:
BEGIN
overdue.analyze_status (
title_in,
start_date_in,
report_info_out,
error_code,
error_msg);
IF error_code != 0
THEN
errpkg.log (...);
GOTO end_of_program;
END IF;
overdue.send_report (
report_info_out,
error_code,
error_msg);
IF error_code != 0
THEN
err.log (...);
GOTO end_of_program;
END IF;
...
<end_of_program>
NULL;
END;
Notice that I must check the status
of each subprogram call; I also usually end up using GOTOs and labels to respond
to the occurrence of failures. Finally, I all too often will hard-code values
such as the "0" as an indication of success. What happens if I ever change the
success indicator?
Using "traditional" PL/SQL logic, the above executable section would look
something like this:
BEGIN
overdue.analyze_status (
title_in,
start_date_in,
report_info_out);
overdue.send_report (report_info_out);
EXCEPTION
WHEN overdue.invalid_date
THEN
errpkg.report_and_go (msg_in => start_date_in);
WHEN OTHERS
THEN
errpkg.report_and_stop;
END;
When conforming to the default
exception-handling model of PL/SQL, executable sections are clean, simple, and
easy to follow. You don't have to check for status after every program call. You
simply include an exception section to trap and deal with crises as they arise.
Encapsulate RAISE_APPLICATION ERROR
If you are raising a "system" exception such as NO_DATA_FOUND, you use RAISE. If
you want to raise an application-specific error, you use RAISE_APPLICATION_ERROR.
If you use the latter, you then have to provide an error number and message.
This leads to unnecessary and damaging hard-coding.
A better approach is to provide a predefined raise procedure that automatically
checks the error number and determines the correct way to raise the error. An
example of such a procedure is in the errpkg.pkg file and is described below.
Instead of writing this:
RAISE_APPLICATION_ERROR (
-20734,
'Employee must be 18 years old.');
you should write this:
errpkg.raise (errnums.en_emp_too_young);
Notice that in this second implementation, I no longer hard-code the error number (which I most likely "picked out of a hat" and assumed that no one else would ever possibly want to use) or the error message. Instead, I open up the errnums package, which contains a set of predefined error numbers, find the one that matches my situation, and then reference the error by its named constant. In other words, the errnums package specification might look something like this (and can be found in errnums.pkg):
CREATE OR REPLACE PACKAGE errnums
IS
exc_bal_too_low EXCEPTION;
en_bal_too_low CONSTANT INTEGER := -20100;
PRAGMA EXCEPTION_INIT (exc_bal_too_low, -20100);
exc_emp_too_young EXCEPTION;
en_emp_too_young CONSTANT INTEGER := -20200;
PRAGMA EXCEPTION_INIT (exc_emp_too_young, -20200);
END errnums;
Listing 2 contains the
implementation of the errpkg.raise procedure. Here is an explanation of the most
interesting elements of the program: Line 2. I pass in the following: an error
number, the default SQLCODE, and an override error message. If none is provided,
I use the default message stored in a messages table. Lines 5 through 7. I
provide the range of error numbers to which RAISE_ APPLICATION_ERROR applies. If
the supplied error number falls in that range, I call the built-in procedure
using the supplied error number and message. If the message is null, I use the
errpkg.errtext function. Lines 9 through 11. I also handle positive numbers for
application-specific error numbers. By handling positive error message numbers,
I am not constrained to error numbers between -20,999 and -20,000, some of which
Oracle also uses (although I stay away from 1 and 100, the only two positive
error numbers that Oracle does use). Lines 14 through 18. I construct a PL/SQL
block that declares a local exception, uses the pragma EXCEPTION_INIT to
associate that exception with the supplied error number, and then raises that
exception.
Using this errpkg.raise procedure, individual developers do not have to make
judgment calls about how they should raise the exception (using RAISE or
RAISE_APPLICATION_ERROR). They simply pass the appropriate error number
(hopefully identified via a named constant) and let the RAISE engine do all the
heavy lifting.
RAISE Is for Errors
You should never use RAISE to abort normal processing in a program and go to the
appropriate WHEN handler. You should raise an exception only when an error has
occurred; do not raise exceptions to control program flow.
The function in Listing 3 demonstrates the problem; it performs a full table
scan of a collection and immediately exits when it finds a match. The exit_function exception is used to abort the function if the input title is
NULL; it is also used as the last line in the function.
In Listing 3, I manage to make it all the way to the end of the function, and
then I am thwarted by having an exception raised. This is very poorly structured
code: it's hard to understand and hard to maintain.
Be on the lookout for a symptom of this misuse of error handling: declared
exceptions whose names describe actions ("exit function"). The name of an
exception should describe an error situation, such as "null name" or "invalid
date."
A better approach is contained in Listing 4.
Raising, Meet Handling
I've shown how you can take a much more organized and robust approach to raising
exceptions: first, write a general exception-handling package that collects all
the tools an application team needs for consistent error management. Second, put
into place clear guidelines for raising exceptions, backed up by a generic raise
program that takes the guesswork out of an individual developer's efforts.
In my next article, I will look at the best practices for handling exceptions
that have been raised.
Steven Feuerstein (steven@stevenfeuerstein.com.) is an authority on the PL/SQL language. Feuerstein is the author of nine books on PL/SQL (all from O'Reilly & Associates), including Oracle PL/SQL Best Practices and Oracle PL/SQL Programming. He is a senior technology advisor for Quest Software.
This article was previously published by Oracle Magazine.
