Predefined PL/SQL Exceptions
An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND
if a SELECT
INTO
statement returns no rows.
To handle other Oracle errors, you can use the OTHERS
handler. The functions SQLCODE
and SQLERRM
are especially useful in the OTHERS
handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT
to associate exception names with Oracle error codes.
PL/SQL declares predefined exceptions globally in package STANDARD
, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the following list:
Brief descriptions of the predefined exceptions follow:
Defining Your Own PL/SQL Exceptions
PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE
statements.
Declaring PL/SQL Exceptions
Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION
. In the following example, you declare an exception named past_due
:
DECLARE past_due EXCEPTION;
Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.
Scope Rules for PL/SQL Exceptions
You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.
Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.
If you redeclare a global exception in a sub-block, the local declaration prevails. So, the sub-block cannot reference the global exception unless it was declared in a labeled block, in which case the following syntax is valid:
block_label.exception_name
The following example illustrates the scope rules:
DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN DECLARE ---------- sub-block begins past_due EXCEPTION; -- this declaration prevails acct_num NUMBER; BEGIN ... IF ... THEN RAISE past_due; -- this is not handled END IF; END; ------------- sub-block ends EXCEPTION WHEN past_due THEN -- does not handle RAISEd exception ... END;
The enclosing block does not handle the raised exception because the declaration of past_due
in the sub-block prevails. Though they share the same name, the two past_due
exceptions are different, just as the two acct_num
variables share the same name but are different variables. Therefore, the RAISE
statement and the WHEN
clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the sub-block or define an OTHERS
handler.
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
To handle error conditions (typically ORA-
messages) that have no predefined name, you must use the OTHERS
handler or the pragma EXCEPTION_INIT
. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT
tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragma EXCEPTION_INIT
in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
where exception_name
is the name of a previously declared exception and the number is a negative value corresponding to an ORA-
error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:
DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... -- Some operation that causes an ORA-00060 error EXCEPTION WHEN deadlock_detected THEN -- handle the error END;
Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR
The procedure RAISE_APPLICATION_ERROR
lets you issue user-defined ORA-
error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.
To call RAISE_APPLICATION_ERROR
, use the syntax
raise_application_error(error_number, message[, {TRUE | FALSE}]);
where error_number
is a negative integer in the range -20000 .. -20999 and message
is a character string up to 2048 bytes long. If the optional third parameter is TRUE
, the error is placed on the stack of previous errors. If the parameter is FALSE
(the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR
is part of package DBMS_STANDARD
, and as with package STANDARD
, you do not need to qualify references to it.
An application can call raise_application_error
only from an executing stored subprogram (or method). When called, raise_application_error
ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.
In the following example, you call raise_application_error
if an employee's salary is missing:
CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS curr_sal NUMBER; BEGIN SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id; IF curr_sal IS NULL THEN /* Issue user-defined error message. */ raise_application_error(-20101, 'Salary is missing'); ELSE UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id; END IF; END raise_salary;
The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE
and SQLERRM
in an OTHERS
handler. Also, it can use the pragma EXCEPTION_INIT
to map specific error numbers returned by raise_application_error
to exceptions of its own, as the following Pro*C example shows:
EXEC SQL EXECUTE /* Execute embedded PL/SQL block using host variables my_emp_id and my_amount, which were assigned values in the host environment. */ DECLARE null_salary EXCEPTION; /* Map error number returned by raise_application_error to user-defined exception. */ PRAGMA EXCEPTION_INIT(null_salary, -20101); BEGIN raise_salary(:my_emp_id, :my_amount); EXCEPTION WHEN null_salary THEN INSERT INTO emp_audit VALUES (:my_emp_id, ...); END; END-EXEC;
This technique allows the calling application to handle error conditions in specific exception handlers.
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
In an exception handler, you can use the built-in functions SQLCODE
and SQLERRM
to find out which error occurred and to get the associated error message. For internal exceptions, SQLCODE
returns the number of the Oracle error. The number that SQLCODE
returns is negative unless the Oracle error is no data found, in which case SQLCODE
returns +100. SQLERRM
returns the corresponding error message. The message begins with the Oracle error code.
For user-defined exceptions, SQLCODE
returns +1 and SQLERRM
returns the message: User-Defined Exception
.
unless you used the pragma EXCEPTION_INIT
to associate the exception name with an Oracle error number, in which case SQLCODE
returns that error number and SQLERRM
returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
If no exception has been raised, SQLCODE
returns zero and SQLERRM
returns the message: ORA-0000: normal, successful completion
.
You can pass an error number to SQLERRM
, in which case SQLERRM
returns the message associated with that error number. Make sure you pass negative error numbers to SQLERRM
. In the following example, you pass positive numbers and so get unwanted results:
DECLARE err_msg VARCHAR2(100); BEGIN /* Get all Oracle error messages. */ FOR err_num IN 1..9999 LOOP err_msg := SQLERRM(err_num); -- wrong; should be -err_num INSERT INTO errors VALUES (err_msg); END LOOP; END;
Passing a positive number to SQLERRM
always returns the message user-defined exception unless you pass +100
, in which case SQLERRM
returns the message no data found. Passing a zero to SQLERRM
always returns the message normal, successful completion.
You cannot use SQLCODE
or SQLERRM
directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
DECLARE err_num NUMBER; err_msg VARCHAR2(100); BEGIN ... EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO errors VALUES (err_num, err_msg); END;
The string function SUBSTR
ensures that a VALUE_ERROR
exception (for truncation) is not raised when you assign the value of SQLERRM
to err_msg
. The functions SQLCODE
and SQLERRM
are especially useful in the OTHERS
exception handler because they tell you which internal exception was raised.
Note: When using pragma RESTRICT_REFERENCES
to assert the purity of a stored function, you cannot specify the constraints WNPS
and RNPS
if the function calls SQLCODE
or SQLERRM
.