Handling Other Unhandled Exceptions

Summary: In this tutorial, you will learn how to handle other unhandled exceptions in the WHEN OTHER clause using SQLCODE and SQLERRM functions.

In this exception handling section, you can include the WHEN OTHERS clause to catch any otherwise unhandled exceptions:

EXCEPTION
    ...
    WHEN OTHERS
        -- catch other exceptionsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Because you handle other non-specific exceptions in the WHEN OTHERS clause, you will need to take advantages of the built-in error functions such as SQLCODE and SQLERRM.

Note that you cannot use SQLCODE or SQLERRM function directly in an SQL statement. Instead, you must first assign their returned values to variables, and then use the variables in the SQL statement.

SQLCODE function #

The SQLCODE function accepts no argument and returns a number code of the most recent exception.

If the exceptions are internal, SQLCODE returns a negative number except for the NO_DATA_FOUND exception which has the number code +100.

If the exception is user-defined, SQLCODE returns +1 or the number that you associated with the exception via the pragma EXCEPTION_INIT.

The SQLCODE is only usable in the exception handling section. If you use the SQLCODE function outside an exception handler, it always returns zero.

This example illustrates how to use the SQLCODE function:

DECLARE
    l_code NUMBER;
    r_customer customers%rowtype;
BEGIN
    SELECT * INTO r_customer FROM customers;
    
    EXCEPTION 
        WHEN OTHERS THEN
        l_code := SQLCODE;  
        dbms_output.put_line('Error code:' || l_code);
END;
/Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we try to fetch too many rows into a record, which results in an error with the following error code:

Error code:-1422Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

SQLERRM function #

The function SQLERRM takes an argument as an error number and returns the error message associated with that error number:

SQLERRM([error_number])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the error_number can be any valid Oracle error number.

If you omit the error_number argument, the function will return the error message associated with the current value of SQLCODE.

Note that the SQLERRM function with no argument is only useful in an exception handler.

This example illustrates how to use the function SQLERRM in an exception handler:

DECLARE
    l_msg VARCHAR2(255);
    r_customer customers%rowtype;
BEGIN
    SELECT * INTO r_customer FROM customers;
    
    EXCEPTION 
        WHEN OTHERS THEN
        l_msg := SQLERRM;  
        dbms_output.put_line(l_msg);
END;
/Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

ORA-01422: exact fetch returns more than requested number of rowsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using SQLCODE and SQLERRM functions example #

The following example inserts a new contact into the contacts table in the sample database. It has an exception handler in the WHERE OTHERS  clause of the exception handling section.

DECLARE
    l_first_name  contacts.first_name%TYPE := 'Flor';
    l_last_name   contacts.last_name%TYPE := 'Stone';
    l_email       contacts.email%TYPE := '[email protected]';
    l_phone       contacts.phone%TYPE := '+1 317 123 4105';
    l_customer_id contacts.customer_id%TYPE := -1;
BEGIN
    -- insert a new contact
    INSERT INTO contacts(first_name, last_name, email, phone, customer_id)
    VALUES(l_first_name, l_last_name, l_email, l_phone, l_customer_id);
    
    EXCEPTION 
        WHEN OTHERS THEN
            DECLARE
                l_error PLS_INTEGER := SQLCODE;
                l_msg VARCHAR2(255) := sqlerrm;
            BEGIN
                CASE l_error 
                WHEN -1 THEN
                    -- duplicate email
                    dbms_output.put_line('duplicate email found ' || l_email);
                    dbms_output.put_line(l_msg);
                    
                WHEN -2291 THEN
                    -- parent key not found
                    dbms_output.put_line('Invalid customer id ' || l_customer_id);
                    dbms_output.put_line(l_msg);
                END CASE;
                -- reraise the current exception
                RAISE;
            END;
            
END;
/
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the exception handler traps two exceptions -1 (duplicate email ) and -2291 (parent key not found). It shows a custom message and reraises the exception using the RAISE statement.

Summary #

• Use the SQLCODE and SQLERRM functions to handle other unhandled exceptions in the WHEN OTHER clause.