Error Management
Part 6 in a series of articles on understanding
and using PL/SQL
ORACLE DATABASE
Even if you write absolutely perfect PL/SQL programs, it is possible and even
likely that something will go wrong and an
error will occur when those programs are
run. How your code responds to and deals
with that error often spells the difference
between a successful application and one
that creates all sorts of problems for users
as well as developers.
This article explores the world of error
management in PL/SQL: the different types
of exceptions you may encounter; when,
why, and how exceptions are raised; how to
define your own exceptions; how you can
handle exceptions when they occur; and how
you can report information about problems
back to your users.
EXCEP TION OVERVIE W
There are three categories of exceptions in
the world of PL/SQL: internally defined, predefined, and user-defined.
An internally defined exception is one that
is raised internally by an Oracle Database
process; this kind of exception always has an
error code but does not have a name unless
it is assigned one by PL/SQL or your own
code. An example of an internally defined
exception is ORA-00060 (deadlock detected
while waiting for resource).
LINDY GROENING
A predefined exception is an internally
defined exception that is assigned a name
by PL/SQL. Most predefined exceptions
are defined in the S TANDARD package (a
package provided by Oracle Database that
defines many common programming elements of the PL/SQL language) and are
among the most commonly encountered
exceptions. One example is ORA-00001,
which is assigned the name DUP_VAL_
ON_INDEX in PL/SQL and is raised when a
unique index constraint is violated.
A user-defined exception is one you have
declared in the declaration section of a
program unit. User-defined exceptions can
be associated with an internally defined
exception (that is, you can give a name to an
otherwise unnamed exception) or with an
application-specific error.
RAISING EXCEP TIONS
In most cases when an exception is raised in
your application, Oracle Database will do the
raising. That is, some kind of problem has
occurred during the execution of your code
and you have no control over this process.
Once the exception has been raised, all you
can do is handle the exception—or let it
“escape” unhandled to the host environment.
You can, however, raise exceptions in your
own code. Why would you want to do this?
Because not every error in an application is
due to a failure of internal processing in the
Oracle Database instance. It is also possible
that a certain data condition constitutes an
error in your application, in which case you
need to stop the processing of your algorithms and, quite likely, notify the user that
something is wrong.
PL/SQL offers two mechanisms for raising
an exception:
•;The RAISE statement
Table 1: Key error information to record
Description
The error code. This code is useful when you need to look up generic
information about what might cause such a problem.
The error message. This text often contains application-specific
data such as the name of the constraint or the column associated
with the problem.
The line on which the error occurred. This capability was added in
Oracle Database 10g Release 2 and is enormously helpful in tracking
down the cause of errors.
The execution call stack. This answers the question “How did I get
here?” and shows you the path through your code to the point at
which DBMS_UTILIT Y. FORMAT_CALL_STACK is called.
How to Get It
SQLCODE
Note: You cannot call this function inside a SQL statement.
SQLERRM or DBMS_UTILI TY.FORMAT_ERROR_STACK
Note: You cannot call SQLERRM inside a SQL statement.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
DBMS_UTILITY.FORMAT_CALL_STACK
