Exception Handling in PL/SQL

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;

Output:

*

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;

Output:

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:

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;

Output:

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;

Output:

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

Read also:

• How to Raise Exception in PL/SQL?
• Oracle PL/SQL Bulk Collect With Save Exceptions Example