In this tutorial, you will learn about exceptions in PL/SQL and how to write code to handle them.
One of the most crucial stages of developing a database application is handling exceptions. Unhandled exceptions pose a threat to application availability, disrupt business operations, and annoy users.
In PL/SQL, an exception can be either predefined by the system or created by the user. A system-defined exception is raised automatically by the Oracle Database, while a user-defined exception is declared and raised by the code itself.
SQLCODE and SQLERRM are two Oracle utility functions that can be used to get the error code and message for the most recent exception.
System-defined Exceptions in PL/SQL
System-defined exceptions are created and managed automatically by the Oracle Database, as their name suggests. There are definitions for them in the Oracle STANDARD package. The database selects the proper exception from the available list whenever an error occurs within a program. Every system-defined exception has a negative error code (except 0 through 100) and a short name that is used when defining the exception handlers.
The PL/SQL code below, for instance, makes use of a SELECT statement to retrieve information about worker 8376. Since employee id 8376 does not exist, a NO DATA FOUND exception is thrown.
/*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;
ORA-01403: no data found ORA-06512: at line 8 ORA-06512: at "SYS.DBMS_SQL", line 1721
Exception Handling Example-1:
Let us rewrite the preceding PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception:
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.
Some of the frequently used system-defined exceptions are listed in the following table along with their short names and ORA error codes:
|Error||Named exception||Comments (raised when:)|
|ORA-00001||DUP_VAL_ON_INDEX||Duplicate value exists|
|ORA-01001||INVALID_CURSOR||Cursor is invalid|
|ORA-01012||NOT_LOGGED_ON||User is not logged in|
|ORA-01017||LOGIN_DENIED||System error occurred|
|ORA-01403||NO_DATA_FOUND||The query returns no data|
|ORA-01422||TOO_MANY_ROWS||A single row query returns multiple rows|
|ORA-01476||ZERO_DIVIDE||An attempt was made to divide a number by zero|
|ORA-01722||INVALID_NUMBER||The number is invalid|
|ORA-06504||ROWTYPE_MISMATCH||Mismatch occurred in row type|
|ORA-06511||CURSOR_ALREADY_OPEN||Cursor is already open|
|ORA-06531||COLLECTION_IS_NULL||Working with NULL collection|
|ORA-06532||SUBSCRIPT_OUTSIDE_LIMIT||Collection index out of range|
|ORA-06533||SUBSCRIPT_BEYOND_COUNT||Collection index out of count|
User-defined Exceptions in PL/SQL
In Oracle, users can create their own exceptions, give them unique names and error codes, and trigger statements in accordance with the logic of their applications. User-defined exceptions are necessary if PL/SQL applications must standardize exception handling.
This is true not only for the purpose of regulating abnormal program flow but also for the purpose of changing the logic of program execution. The RAISE statement is used between the block's BEGIN and END tags to throw the user-defined exceptions.
A user-defined exception can be declared in one of three ways:
In the declaration section, name the variable of type EXCEPTION. Use the RAISE statement inside the main body of the program to do so. Take care of it in the EXCEPTION clause. Take note that there is no mention of an error number.
Invoke PRAGMA EXCEPTION INIT to declare the EXCEPTION variable and assign it a standard error value.
Exception Handling Example-2:
A custom error code and message can be declared with the RAISE APPLICATION ERROR function. An example of a user-defined exception being declared and then raised within the main body of a program using PL/SQL is as follows:
DECLARE /*Declare the local variables and initialize with the bind variables*/ L_DIVISOR NUMBER := 0; L_DIVIDEND NUMBER := 10; 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.
PL/SQL RAISE_APPLICATION_ERROR Procedure
An Oracle-provided procedure, RAISE APPLICATION ERROR, triggers an exception with a user-specified message. In the declarative portion of the PL/SQL code, the exception can be predefined if desired.
The RAISE APPLICATION ERROR method has the following syntax:
RAISE APPLICATION ERROR (error number, error message[, TRUE | FALSE])
The error number parameter is required in this syntax, and its allowed values are 20000 through 20999. The message displayed alongside an exception is user-defined and is stored in the variable error message. The final argument is a callback that can be used to append the exception error code to the error stack.
Exception Handling Example-3:
The following PL/SQL code displays all staff members who started working for the company after the specified date. In the event that the joining date is prior to the specified date, the program must throw an exception. With the RAISE APPLICATION ERROR, the block generates an exception with code 20005, and the screen displays the proper error message.
DECLARE /*Declare the birth date */ L_DOB_MON DATE := to_date('01-DEC-1981', 'dd-mon-yyyy'); /*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;
ORA-20005: Hiredate earlier than the given date!! Check for another employee ORA-06512: at line 16 ORA-06512: at line 16 ORA-06512: at "SYS.DBMS_SQL", line 1721