During runtime, the abnormal program flow which occurs within a precompiled program unit with the actual data is known as an
exception. Such errors can be trapped in the EXCEPTION section of a PL/SQL block. The exception handlers within the section can capture the appropriate error and redirect the program flow for an alternative or final task. An efficient exception handling ensures safe and secure termination of the program. The situation without exceptions may become serious if the program involves transactions and the program doesn't handle the appropriate exception, thus ending up in abrupt termination of the program.
There are two types of exceptions—system-defined exceptions and user defined exceptions. While system defined exceptions are implicitly raised by the Oracle server, user-defined exceptions follow different ways to be explicitly raised within the program.
In addition, Oracle avails two utility functions, SQLCODE and SQLERRM, to retrieve the error code and message for the last occurred exception.
As the name suggests, the system-defined exceptions are defined and maintained implicitly by the Oracle server. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside the program. The Oracle server matches and identifies the appropriate exception from the available set of exceptions. Majorly, these exceptions have a negative error code associated with it. In addition to the error code and error message, the system-defined exceptions have a short name which is used with the exception handlers.
For example, ORA-01422 is the error code for the TOO_MANY_ROWS exception whose error message is "exact fetch returns more than requested number of rows". But the name is required only in exception handlers.
The PL/SQL block contains a SELECT statement which selects the name and salary of an employee whose employee ID is one of the declared variables. Note that such SELECT statements are more prone to the NO_DATA_FOUND exception.
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/SQL> SET SERVEROUT ON/*Start the PL/SQL block*/SQL> 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, SALINTO L_ENAME, L_SALFROM EMPLOYEESWHERE EMPNO = L_EMPID;EXCEPTION/*Exception Handler when no data is fetched from the table*/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 837 PL/SQL procedure successfully completed.
The following table consolidates some of the common system-defined exceptions along with their 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 |
|
|
|
A number is attempted to divide 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 |
Sometimes, the programs are expected to follow agile convention norms of an application. The programs must have standardized error codes and messages. Oracle gives flexibility in declaring and implementing your own exceptions through user-defined exceptions.
Unlike system-defined exceptions, they are raised explicitly in the BEGIN…END section 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 here no error code is involved.Declare the
EXCEPTIONvariable and associate it with a standard error number usingPRAGMA EXCEPTION_INIT.Note
A Pragma is a clue 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 the Oracle server, but not defined as PL/SQL exceptions.Use the
RAISE_APPLICATION_ERRORto declare own 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*/SQL> SET SERVEROUTPUT ON/*Declare a bind variable M_DIVISOR*/SQL> VARIABLE M_DIVISOR NUMBER;/*Declare a bind variable M_DIVIDEND*/SQL> VARIABLE M_DIVIDEND NUMBER;/*Assign value to M_DIVISOR as zero*/SQL> EXEC :M_DIVISOR := 0; PL/SQL procedure successfully completed./*Assign value to M_DIVIDEND as 10/SQL> EXEC :M_DIVIDEND := 10; PL/SQL procedure successfully completed./*Start the PL/SQL block*/SQL> 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 and execute the PL/SQL block again*/SQL> EXEC :M_DIVISOR := 2; PL/SQL procedure successfully completed. SQL> / The result : 5 PL/SQL procedure successfully completed.
Oracle gives privilege to the database programmers to create their own error number and associate an error message, too. These are dynamic user defined exceptions and are done through an Oracle-supplied method, RAISE_APPLICATION_ERROR. It can be implemented either in the executable section to capture specific and logical errors, or it can be used in the exception section to handle errors of a generic nature.
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 formal parameter whose value must be in the range of -20000 to -20999. The second parameter, error_message, corresponds to the error number and appears with the exception when raised in the program. The last parameter is the optional parameter which allows the error to be added to the current error stack. By default, its value is FALSE.
The following program rewrites the last program by creating a user-defined exception, dynamically (note that it doesn't have the EXCEPTION type variable):
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/SQL> SET SERVEROUTPUT ON/*Declare a bind variable M_DIVISOR*/SQL> VARIABLE M_DIVISOR NUMBER;/*Declare a bind variable M_DIVIDEND*/SQL> VARIABLE M_DIVIDEND NUMBER;/*Assign value to M_DIVISOR as zero*/SQL> EXEC :M_DIVISOR := 0; PL/SQL procedure successfully completed./*Assign value to M_DIVIDEND as 10/SQL> EXEC :M_DIVIDEND := 10; PL/SQL procedure successfully completed./*Start the PL/SQL block*/SQL> DECLARE/*Declare the local variables and initialize them with bind variables*/L_DIVISOR NUMBER := :M_DIVISOR; L_DIVIDEND NUMBER := :M_DIVIDEND; L_QUOT NUMBER; BEGIN/*Raise the exception using RAISE_APPLICATION_ERROR is the divisor is zero*/IF L_DIVISOR = 0 THEN RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero'); END IF; L_QUOT := L_DIVIDEND/L_DIVISOR; DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT); EXCEPTION/*Print appropriate message in OTHERS exception handler*/WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / ORA-20005: Divisor cannot be equal to zero PL/SQL procedure successfully completed./*Assign a non zero value to M_DIVISOR and check the output of the PL/SQL block*/SQL> EXEC :M_DIVISOR := 2; PL/SQL procedure successfully completed. SQL> / The result : 5 PL/SQL procedure successfully completed.
As soon as the exception is raised through RAISE_APPLICATION_ERROR, the program control skips the further execution and jumps to the EXCEPTION section. As there is no exception name mapped against this error code, only OTHERS exception handler can handle the exception.
If a EXCEPTION variable has been declared and mapped to the same user-defined error number, the exception handler can be created with the exception variable. Let us rewrite the preceding program to include an exception variable and suitable exception handler. The following program demonstrates the working of user-defined exceptions and dynamic user-defined exceptions in a single program:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/SQL> SET SERVEROUTPUT ON/*Declare a bind variable M_DIVISOR*/SQL> VARIABLE M_DIVISOR NUMBER;/*Declare a bind variable M_DIVIDEND*/SQL> VARIABLE M_DIVIDEND NUMBER;/*Assign value to M_DIVISOR as zero*/SQL> EXEC :M_DIVISOR := 0; PL/SQL procedure successfully completed./*Assign value to M_DIVIDEND as 10/SQL> EXEC :M_DIVIDEND := 10; PL/SQL procedure successfully completed./*Start the PL/SQL block*/SQL> DECLARE/*Declare an exception variable*/NOCASE EXCEPTION;/*Declare the local variables and initialize them with bind variables*/L_DIVISOR NUMBER := :M_DIVISOR; L_DIVIDEND NUMBER := :M_DIVIDEND; L_QUOT NUMBER;/*Map the exception with a non predefined error number*/PRAGMA EXCEPTION_INIT(NOCASE,-20005); BEGIN/*Raise the exception using RAISE statement if the divisor is zero*/IF L_DIVISOR = 0 THEN RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero'); END IF; L_QUOT := L_DIVIDEND/L_DIVISOR; DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT); EXCEPTION/*Include exception handler for NOCASE exception*/WHEN NOCASE THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / ORA-20005: Divisor cannot be equal to zero PL/SQL procedure successfully completed.
Exception propagation is an important concept when dealing with nested blocks. A propagating exception always searches for the appropriate exception handler until its last host. The search starts from the EXCEPTION section of the block, that raised it, and continues abruptly until the host environment is reached. As soon as the exception handler is found, the program control resumes the normal flow.
The following cases demonstrate the propagation of exception which is raised in the innermost block:
Case 1: The following diagram shows the state of a nested PL/SQL block. The inner block raises an exception which is handled in its own
EXCEPTIONsection:
Exception A is raised by the inner block. The inner block handles the exception A within its scope. After the exception is handled, the program control resumes the flow with statements after the inner block in the outer block.
Case 2: The following diagram shows the state of a nested PL/SQL block where the inner block raises an exception but does not handle the same in its own
EXCEPTIONsection. TheEXCEPTIONsection of the outer block handles the raised exception:
The inner block raises the exception A but does not handle it, so it gets propagated to the
EXCEPTIONsection of the enclosing outer block. Note the abrupt skipping of statements in the outer block.Now, the outer block handles the exception A. The exception propagated from the inner block is handled in the outer block and is then terminated.
Case 3: The following diagram shows the state of a nested PL/SQL block where both the inner and outer block doesn't handle the exception raised in the inner block:
Handling for exception A is missing in the inner as well as the outer block. As a result, the unhandled exception error is raised. The exception is propagated to the host with an error message and the program is terminated abruptly.