{"id":2859,"date":"2019-08-09T00:40:30","date_gmt":"2019-08-09T07:40:30","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2859"},"modified":"2025-05-24T20:05:23","modified_gmt":"2025-05-25T03:05:23","slug":"oracle-sqlcode","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/oracle-sqlcode\/","title":{"rendered":"Handling Other Unhandled Exceptions"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to handle other unhandled exceptions in the WHEN OTHER<\/code> clause using SQLCODE<\/code> and SQLERRM<\/code> functions.<\/p>\n\n\n\n

In this exception<\/a> handling section, you can include the WHEN OTHERS<\/code> clause to catch any otherwise unhandled exceptions:<\/p>\n\n\n

EXCEPTION<\/span>\n    ...\n    WHEN<\/span> OTHERS\n        -- catch other exceptions<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because you handle other non-specific exceptions in the WHEN OTHERS<\/code> clause, you will need to take advantages of the built-in error functions such as SQLCODE<\/code> and SQLERRM<\/code>.<\/p>\n\n\n\n
SQLCODE function #<\/a><\/h2>\n\n\n\n
The SQLCODE<\/code> function accepts no argument and returns a number code of the most recent exception.<\/p>\n\n\n\n
If the exceptions are internal, SQLCODE<\/code> returns a negative number except for the NO_DATA_FOUND<\/code> exception which has the number code +100.<\/p>\n\n\n\n
If the exception is user-defined, SQLCODE<\/code> returns +1 or the number that you associated with the exception via the pragma EXCEPTION_INIT<\/code>.<\/p>\n\n\n\n
The SQLCODE<\/code> is only usable in the exception handling section. If you use the SQLCODE<\/code> function outside an exception handler, it always returns zero.<\/p>\n\n\n\n
This example illustrates how to use the SQLCODE<\/code> function:<\/p>\n\n\n
DECLARE<\/span>\n    l_code NUMBER;\n    r_customer customers%rowtype<\/span>;\nBEGIN<\/span>\n    SELECT<\/span> * INTO<\/span> r_customer FROM<\/span> customers;\n    \n    EXCEPTION<\/span> \n        WHEN<\/span> OTHERS THEN<\/span>\n        l_code := SQLCODE;  \n        dbms_output.put_line('Error code:'<\/span> || l_code);\nEND<\/span>;\n\/<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we try to fetch too many rows into a record, which results in an error with the following error code:<\/p>\n\n\n
Error code:-1422<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQLERRM function #<\/a><\/h2>\n\n\n\n
The function SQLERRM<\/code> takes an argument as an error number and returns the error message associated with that error number:<\/p>\n\n\n
SQLERRM<\/span>([error_number])<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the error_number<\/code> can be any valid Oracle error number.<\/p>\n\n\n\n
If you omit the error_number<\/code> argument, the function will return the error message associated with the current value of SQLCODE<\/code>.<\/p>\n\n\n\n
Note that the SQLERRM<\/code> function with no argument is only useful in an exception handler.<\/p>\n\n\n\n
This example illustrates how to use the function SQLERRM<\/code> in an exception handler:<\/p>\n\n\n
DECLARE<\/span>\n    l_msg VARCHAR2(255<\/span>);\n    r_customer customers%rowtype<\/span>;\nBEGIN<\/span>\n    SELECT<\/span> * INTO<\/span> r_customer FROM<\/span> customers;\n    \n    EXCEPTION<\/span> \n        WHEN<\/span> OTHERS THEN<\/span>\n        l_msg := SQLERRM<\/span>;  \n        dbms_output.put_line(l_msg);\nEND<\/span>;\n\/<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
ORA-01422<\/span>: exact fetch<\/span> returns<\/span> more than requested number of<\/span> rows<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using SQLCODE and SQLERRM functions example #<\/a><\/h2>\n\n\n\n
The following example inserts<\/a> a new contact into the contacts<\/code> table in the sample database<\/a>. It has an exception handler in the WHERE OTHERS<\/code>\u00a0 clause of the exception handling section.<\/p>\n\n\n
DECLARE<\/span>\n    l_first_name  contacts.first_name%TYPE<\/span> := 'Flor'<\/span>;\n    l_last_name   contacts.last_name%TYPE<\/span> := 'Stone'<\/span>;\n    l_email       contacts.email%TYPE<\/span> := 'flor.stone@raytheon.com'<\/span>;\n    l_phone       contacts.phone%TYPE<\/span> := '+1 317 123 4105'<\/span>;\n    l_customer_id contacts.customer_id%TYPE<\/span> := -1<\/span>;\nBEGIN<\/span>\n    -- insert a new contact<\/span>\n    INSERT<\/span> INTO<\/span> contacts(first_name, last_name, email, phone, customer_id)\n    VALUES<\/span>(l_first_name, l_last_name, l_email, l_phone, l_customer_id);\n    \n    EXCEPTION<\/span> \n        WHEN<\/span> OTHERS THEN<\/span>\n            DECLARE<\/span>\n                l_error PLS_INTEGER := SQLCODE;\n                l_msg VARCHAR2(255<\/span>) := sqlerrm<\/span>;\n            BEGIN<\/span>\n                CASE<\/span> l_error \n                WHEN<\/span> -1<\/span> THEN<\/span>\n                    -- duplicate email<\/span>\n                    dbms_output.put_line('duplicate email found '<\/span> || l_email);\n                    dbms_output.put_line(l_msg);\n                    \n                WHEN<\/span> -2291<\/span> THEN<\/span>\n                    -- parent key not found<\/span>\n                    dbms_output.put_line('Invalid customer id '<\/span> || l_customer_id);\n                    dbms_output.put_line(l_msg);\n                END<\/span> CASE<\/span>;\n                -- reraise the current exception<\/span>\n                RAISE<\/span>;\n            END<\/span>;\n            \nEND<\/span>;\n\/\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
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<\/code> statement.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQLCODE<\/code> and SQLERRM<\/code> functions to handle other unhandled exceptions in the WHEN OTHER<\/code> clause.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that you cannot use SQLCODE<\/code> or SQLERRM<\/code> function directly in an SQL statement. Instead, you must first assign their returned values to variables<\/a>, and then use the variables in the SQL statement.<\/p>\n\n\n\n