PL/SQL Exception

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

Introduction to PL/SQL Exceptions #

PL/SQL treats all errors in an anonymous block, procedure, or function as exceptions.The exceptions can have different causes, such as:

• Coding mistakes
• Software bugs
• 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 usual.

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

A PL/SQL block may have an exception-handling section with one or more exception handlers.

Here’s 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 arises, then the other_exception_handler runs.

After an exception handler executes, control transfers to the following statement of the enclosing block.

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

If an exception occurs but there is no exception handler, 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 input and returns the customer’s 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 0, Oracle will issue the following error:

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

The ORA-01403 is a predefined exception.

Note that the following line does not execute because control is 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 error 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 existCode 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 rowsCode 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 exceptions and issue the following message:

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

PL/SQL exception categories #

PL/SQL has three exception categories:

• Internally defined exceptions are errors that arise from the Oracle Database environment. The runtime system raises internally defined exceptions automatically. ORA-27102 (out of memory) is one example. Note that internally defined exceptions do not have names but an error code.
• Predefined exceptions are errors that 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 exceptions defined by users like you. User-defined exceptions must be raised explicitly.

The following table illustrates the differences between exception categories.

Summary #

• PL/SQL treats all errors as exceptions.
• Use exception handlers to handle exceptions, allowing programs to continue execution.
• When an exception occurs, PL/SQL stops the current block and transfers control to the exception-handling section.
• PL/SQL has three exception categories: internally defined, predefined, and user-defined.
• If an exception occurs and you don’t handle it, PL/SQL will propagate it.