 |
|
Exceptions and Exception
Handling
Oracle Tips by
Burleson
|
PL/SQL was originally based on the Ada programming language (once
widely used in software written for the Department of Defense). One of
the primary reasons that Ada was chosen as the mother language for
PL/SQL is the concept of exceptions. Figure 2.10 illustrates how
exceptions are raised and handled.
When a PL/SQL block encounters an error condition, an exception is
raised. Each PL/SQL block can have an exception handler prior to the
END statement. In this section of the block, the developer
specifies what actions should be taken for specific exceptions. If no
exception handler is declared in a block, the exception is raised to
the calling PL/SQL block.
Table 2.4 lists the standard exceptions that you will encounter
most frequently.
|
Table 2.4 The five most commonly encountered exceptions. |
|
Exception |
Situation |
|
NO_DATA_FOUND |
You executed a query for which no rows were
found. |
|
TOO_MANY_ROWS |
You executed a query for which more than one
row was found. Your query was only structured to receive a single
row in the result set. |
|
DUP_VAL_ON_INDEX |
You attempted to insert a row into a table,
but the row violates the table’s primary key or unique index. |
|
VALUE_ERROR |
You assigned a value to a variable that is
too short to hold the value. This occurs most commonly with
variables of type varchar2 or char, but this can also
happen to variables of other types. |
|
INVALID_NUMBER |
You referenced a value containing a character
in an expression that attempted to convert the value to a number,
either explicitly or implicitly. |
If no exception is raised from a PL/SQL block (or stored PL/SQL
object), the calling object assumes that the block completed
successfully. Unlike a function written in C, there are no status
values that are found in the database environment to indicate if a
procedure or function call encountered an error.
PRAGMAs
Most of the code that you write will be executable code, with one
notable exception. A PRAGMA is not executable code; instead, a
PRAGMA is an instruction to the PL/SQL compiler. PL/SQL
provides two distinct uses of the PRAGMA statement:
exception_init and restrict_references.
The exception_init PRAGMA instructs Oracle to assign a name
to a standard Oracle error message that does not have an associated
named exception. In this example, the ORA-00942 error is renamed to a
user-defined exception xTABLEDOESNOTEXIST:
DECLARE
xTableDoesNotExist EXCEPTION;
PRAGMA exception_init (xTableDoesNotExist -942);
...
END;
The RESTRICT_REFERENCES PRAGMA instructs Oracle about the
purity level of a packaged PL/SQL function. In this example, the
Conversions package instructs the database that the function
Feet_To_Meters does not alter any database or package states:
PACKAGE Conversions
IS
FUNCTION Feet_To_Meters (nFeet IN number) RETURN number;
RESTRICT_REFERENCES (Feet_To_Meters, WNDS, RNDS, WNPS, RNPS);
END Conversions;
Once the function has been declared in this fashion, it can be
execute inside a DML statement without receiving an error message, as
shown in this example:
SELECT Feet_To_Meters (3.45)
FROM DUAL;
There are four purity levels that can be asserted for packaged
functions:
-
WNDS—The function does not write to any
tables.
-
RNDS—The function does not read from any
tables.
-
WNPS—The function does not modify any
variables inside the package.
-
RNPS—The function does not read any
variables inside the package.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
