Exception handling in PL/SQL
If a program shows an unusual and unexpected flow during runtime, which might result in abnormal termination of the program, the situation is said to be an exception. Such errors must be trapped and handled in the EXCEPTION section of the PL/SQL block. The exception handlers can suppress the abnormal termination with an alternative and secured action.
Exception handling is one of the important steps of database programming. Unhandled exceptions can result in unplanned application outages, impact business continuity, and frustrate end users.
There are two types of exceptions—system-defined and user-defined. While the Oracle Database implicitly raises a system-defined exception, a user-defined exception is explicitly declared and raised within the program unit.
In addition, Oracle provides two utility functions, SQLCODE and SQLERRM, to retrieve the error code and message for the most recent exception.
System-defined exceptions
As the name implies, system-defined exceptions are defined and maintained implicitly by the Oracle Database. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside a program, the database picks up the appropriate exception from the available list. All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name, which is used while specifying the exception handlers.
For example, the following PL/SQL program includes a SELECT statement to select details of employee 8376. It raises NO_DATA_FOUND exception because employee id 8376 doesn't exist.
SET SERVEROUTPUT ON /*Declare the PL/SQL block */ DECLARE L_ENAME VARCHAR2 (100); L_SAL NUMBER; L_EMPID NUMBER := 8376; BEGIN /*Write a SELECT statement */ SELECT ENAME, SAL INTO L_ENAME, L_SAL FROM EMP WHERE EMPNO = L_EMPID; END; / DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 8
Let us rewrite the preceding PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare the local variables*/
L_ENAME VARCHAR2 (100);
L_SAL NUMBER;
L_EMPID NUMBER := 8376;
BEGIN
/*SELECT statement to fetch the name and salary details of the employee*/
SELECT ENAME, SAL
INTO L_ENAME, L_SAL
FROM EMP
WHERE EMPNO = L_EMPID;
EXCEPTION
/*Exception Handler */
WHEN NO_DATA_FOUND THEN
/*Display an informative message*/
DBMS_OUTPUT.PUT_LINE ('No Employee exists with the id '||L_EMPID);
END;
/
No Employee exists with the id 8376
PL/SQL procedure successfully completed.The following table lists some of the commonly used system-defined exceptions along with their short name and ORA error code:
|
Error |
Named exception |
Comments (raised when:) |
|---|---|---|
|
|
|
Duplicate value exists |
|
|
|
Cursor is invalid |
|
|
|
User is not logged in |
|
|
|
System error occurred |
|
|
|
The query returns no data |
|
|
|
A single row query returns multiple rows |
|
|
|
An attempt was made to divide a number by zero |
|
|
|
The number is invalid |
|
|
|
Mismatch occurred in row type |
|
|
|
Cursor is already open |
|
|
|
Working with |
|
|
|
Collection index out of range |
|
|
|
Collection index out of count |
User-defined exceptions
Oracle allows users to create custom exceptions, specify names, associate error codes, and raise statements in line with the implementation logic. If PL/SQL applications are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.
There are three ways of declaring user-defined exceptions:
- Declare the
EXCEPTIONtype variable in the declaration section. Raise it explicitly in the program body using theRAISEstatement. Handle it in theEXCEPTIONsection. Note that no error code is involved here. - Declare the
EXCEPTIONvariable and associate it with a standard error number usingPRAGMA EXCEPTION_INIT.Note
A Pragma is a directive to the compiler to manipulate the behavior of the program unit during compilation, and not at the time of execution.
PRAGMA EXCEPTION_INITcan also be used to map an exception to a non-predefined exception. These are standard errors from Oracle but not defined as PL/SQL exceptions. - Use the
RAISE_APPLICATION_ERRORto declare a dedicated error number and error message.
The following PL/SQL block declares a user-defined exception and raises it in the program body:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON
/*Declare a bind variable M_DIVISOR*/
VARIABLE M_DIVISOR NUMBER;
/*Declare a bind variable M_DIVIDEND*/
VARIABLE M_DIVIDEND NUMBER;
/*Assign value to M_DIVISOR as zero*/
EXEC :M_DIVISOR := 0;
PL/SQL procedure successfully completed.
/*Assign value to M_DIVIDEND as 10/
EXEC :M_DIVIDEND := 10;
PL/SQL procedure successfully completed.
/*Start the PL/SQL block*/
DECLARE
/*Declare the local variables and initialize with the bind variables*/
L_DIVISOR NUMBER := :M_DIVISOR;
L_DIVIDEND NUMBER := :M_DIVIDEND;
L_QUOT NUMBER;
/*Declare an exception variable*/
NOCASE EXCEPTION;
BEGIN
/*Raise the exception if Divisor is equal to zero*/
IF L_DIVISOR = 0 THEN
RAISE NOCASE;
END IF;
L_QUOT := L_DIVIDEND/L_DIVISOR;
DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
EXCEPTION
/*Exception handler for NOCASE exception*/
WHEN NOCASE THEN
DBMS_OUTPUT.PUT_LINE('Divisor cannot be equal to zero');
END;
/
Divisor cannot be equal to zero
PL/SQL procedure successfully completed.
/*Assign a non zero value to M_DIVISOR*/
EXEC :M_DIVISOR := 2;
PL/SQL procedure successfully completed.
/*Re-execute the block */
SQL> /
The result : 5
PL/SQL procedure successfully completed.The RAISE_APPLICATION_ERROR procedure
The
RAISE_APPLICATION_ERROR is an Oracle-supplied procedure that raises a user-defined exception with a custom exception message. The exception can be optionally pre-defined in the declarative section of the PL/SQL.
The syntax for the RAISE_APPLICATION_ERROR procedure is as follows:
RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])In this syntax, the error_number parameter is a mandatory parameter with the error value ranging between 20000 to 20999. error_message is the user-defined message that appears along with the exception. The last parameter is an optional argument that is used to add the exception error code to the current error stack.
The following PL/SQL program lists the employees who have joined the organization after the given date. The program must raise an exception if the date of joining is before the given date. The block uses RAISE_APPLICATION_ERROR to raise the exception with an error code 20005, and an appropriate error message appears on the screen:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block */
DECLARE
/*Declare the birth date */
L_DOB_MON DATE := '01-DEC-1981';
/*Declare a cursor to filter employees who were hired on birthday month*/
CURSOR C IS
SELECT empno, ename, hiredate
FROM emp;
BEGIN
FOR I IN C
LOOP
/*Raise exception, if birthdate is later than the hiredate */
IF i.hiredate < l_dob_mon THEN
RAISE_APPLICATION_ERROR (-20005,'Hiredate earlier than the given date!! Check for another employee');
ELSE
DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
END IF;
END LOOP;
END;
/
*
ERROR at line 1:
ORA-20005: Hiredate earlier than the given date!! Check for another employee
ORA-06512: at line 11In the preceding example, note that the exception name is not used to create the exception handler. Just after the exception is raised through RAISE_APPLICATION_ERROR, the program is terminated.
If you wish to have a specific exception handler for the exceptions raised through RAISE_APPLICATION_ERROR, you must declare the exception in the declarative section and associate the error number using PRAGMA EXCEPTION_INIT. Check the following PL/SQL program:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON
/*Start the PL/SQL block */
DECLARE
/*Declare the birth date */
L_DOB_MON DATE := '01-DEC-1981';
/*Declare the exception variable */
INVALID_EMP_DATES EXCEPTION;
PRAGMA EXCEPTION_INIT(INVALID_EMP_DATES,-20005);
/*Declare a cursor to filter employees who were hired on birthday month*/
CURSOR C IS
SELECT ename, deptno, hiredate
FROM emp;
BEGIN
FOR I IN C
LOOP
/*Raise exception, if birthdate is later than the hiredate */
IF i.hiredate < l_dob_mon THEN
RAISE INVALID_EMP_DATES;
ELSE
DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
END IF;
END LOOP;
EXCEPTION
WHEN INVALID_EMP_DATES THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'Hiredate earlier than the given date!! Check for another employee');
END;
/
ORA-20005: Hiredate earlier than the given date!! Check for another employee
PL/SQL procedure successfully completed.Exception propagation
Until now, we have seen that, as soon as the exception is raised in the procedural section of a PL/SQL block, the control jumps to the exception section and chooses the appropriate exception handler. The non-existence of the exception handler may lead to the abnormal termination of the program.
In the case of nested PL/SQL blocks, if the exception is raised in an inner block, the program control flows down to the exception section of the inner block. If the inner block handles the exception, it is executed and the program control returns to the next executable statement in the outer block.
If the inner block does not handle the exception, the program control continues to search for the appropriate handler and propagates to the exception section of the outer block. Yes, the execution of the outer block is skipped and the program control lands straight in to the exception section. The program control will continue to propagate the unhandled exception in the outer blocks until the appropriate one is found and handled.
For example, the following PL/SQL program contains a child block within the parent block:
/*Parent block*/
DECLARE
...
BEGIN
/*Outer block executable statements*/
...
/*Child Block*/
DECLARE
...
BEGIN
...
/*Inner block executable statements*/
...
EXCEPTION
/*Inner block exception handlers*/
END;
...
/*Outer block executable statements*/
EXCEPTION
/*Outer block exception handlers*/
END;If the exception is raised in one of the /*Inner block executable statements*/, the control flows to /*Inner block exception handlers*/. If the appropriate exception handler is not found, it propagates straight to the /*Outer block exception handlers*/ and execution of /*Outer block executable statements*/ is skipped.
When working with nested PL/SQL blocks, developers must be cautious while coding exception handling logic. The exception propagation should be thoroughly tested to build fail‑proof applications.
