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 exceptions
Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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

Error code:-1422
Code language: SQL (Structured Query Language) (sql)

 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Here is the output:

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

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 := 'flor.stone@raytheon.com'; 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: SQL (Structured Query Language) (sql)

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.

In this tutorial, you have learned how to handle other unhandled exceptions in the WHEN OTHER clause using the SQLCODE and SQLERRM functions.

Was this tutorial helpful?