PL/SQL Exception

Summary: in this tutorial, you will learn about PL/SQL exception and how to write exception handler to handle exceptions.

Introduction to PL/SQL Exceptions

PL/SQL treats all errors that occur in an anonymous block, procedure, or function as exceptions. The exceptions can have different causes such as coding mistakes, bugs, even hardware failures.

It is not possible to anticipate all potential exceptions, however, you can write code to handle exceptions to enable the program to continue running as normal.

The code that you write to handle exceptions is called an exception handler.

A PL/SQL block can have an exception-handling section, which can have one or more exception handlers.

Here is the basic syntax of the exception-handling section:

BEGIN
    -- executable section
    ...
    -- exception-handling section
    EXCEPTION 
        WHEN e1 THEN 
            -- exception_handler1
        WHEN e2 THEN 
            -- exception_handler1
        WHEN OTHERS THEN
            -- other_exception_handler
END;
Code language: SQL (Structured Query Language) (sql)

In this syntax, e1, e2 are exceptions.

When an exception occurs in the executable section, the execution of the current block stops and control transfers to the exception-handling section.

If the exception e1 occurred, the exception_handler1 runs. If the exception e2 occurred, the exception_handler2 executes. In case any other exception raises, then the other_exception_handler runs.

After an exception handler executes, control transfers to the next statement of the enclosing block. If there is no enclosing block, then the control returns to the invoker if the exception handler is in a subprogram or host environment (SQL Developer or SQL*Plus) if the exception handler is in an anonymous block.

If an exception occurs but there is no exception handler, then the exception propagates, which we will discuss in the unhandled exception propagation tutorial.

PL/SQL exception examples

Let’s take some examples of handling exceptions.

PL/SQL NO_DATA_FOUND exception example

The following block accepts a customer id as an input and returns the customer name :

DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer name by id
    SELECT name INTO l_name
    FROM customers
    WHERE customer_id = l_customer_id;

    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);
    
END;
/
Code language: SQL (Structured Query Language) (sql)

If you execute the block and enter the customer id as zero, Oracle will issue the following error:

ORA-01403: no data found
Code language: SQL (Structured Query Language) (sql)

The ORA-01403 is a predefined exception.

Note that the following line does not execute at all because control transferred to the exception handling section.

dbms_output.put_line('Customer name is ' || l_name);
Code language: SQL (Structured Query Language) (sql)

To issue a more meaningful message, you can add an exception-handling section as follows:

DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT NAME INTO l_name
    FROM customers
    WHERE customer_id = l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('customer name is ' || l_name);

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
END;
/
Code language: SQL (Structured Query Language) (sql)

If you execute this code block and enter the customer id 0, you will get the following message:

Customer 0 does not exist
Code language: SQL (Structured Query Language) (sql)

PL/SQL TOO_MANY_ROWS exception example

First, modify the code block in the above example as follows and execute it:

DECLARE
    l_name customers.name%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT name INTO l_name
    FROM customers
    WHERE customer_id <= l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
END;
/
Code language: SQL (Structured Query Language) (sql)

Second, enter the customer id 10 and you’ll get the following error:

ORA-01422: exact fetch returns more than requested number of rows
Code language: SQL (Structured Query Language) (sql)

This is another exception called TOO_MANY_ROWS which was not handled by the code.

Third, add the exception handler for the TOO_MANY_ROWS exception:

DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT NAME INTO l_name
    FROM customers
    WHERE customer_id > l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
        WHEN TOO_MANY_ROWS THEN
            dbms_output.put_line('The database returns more than one customer');    
END;
/
Code language: SQL (Structured Query Language) (sql)

Finally, if you execute the code, enter 10 as the customer id. You will see that the code will not raise any exception and issue the following message:

The database returns more than one customer
Code language: SQL (Structured Query Language) (sql)

PL/SQL exception categories

PL/SQL has three exception categories:

• Internally defined exceptions are errors which arise from the Oracle Database environment. The runtime system raises the internally defined exceptions automatically. ORA-27102 (out of memory) is one example of Internally defined exceptions. Note that Internally defined exceptions do not have names, but an error code.
• Predefined exceptions are errors which occur during the execution of the program. The predefined exceptions are internally defined exceptions that PL/SQL has given names e.g., NO_DATA_FOUND, TOO_MANY_ROWS.
• User-defined exceptions are custom exception defined by users like you. User-defined exceptions must be raised explicitly.

The following table illustrates the differences between exception categories.

In this tutorial, you have learned about the PL/SQL exceptions and how to write exception handlers to handle the possible exceptions in a block.