Oracle SQLCODE and SQLERRM Function Guide, FAQ, & Examples

Do you need to find out more information about your errors and exceptions in PL/SQL?

You can use the SQLCODE and SQLERRM functions to do that. Learn how the functions work and see some examples in this article.

Purpose of the SQLCODE and SQLERRM Functions

The SQLCODE function returns the number code of the most recent exception.

This number code is an ANSI-standard code, which means it is the same for all relational databases. It is different from the ORA codes, which you may also get when working with Oracle databases.

The SQLERRM function returns the error message that relates to the error number provided.

This function is similar to the SQLCODE function, where the only real useful place for it is inside an exception handler in PL/SQL. We’ll see more on this later in the article.

 

SQLCODE Syntax and Parameters

The SQLCODE function is pretty simple:

SQLCODE

There are no parameters for the Oracle SQLCODE function.

 

SQLERRM Syntax and Parameters

The syntax of this function is:

SQLERRM ( error_number )

The parameters of the SQLERRM function are:

• error_number (optional): A valid Oracle error number.

Now, it seems like a simple function, but there are some things to keep in mind:

• If the error_number is omitted, then it returns the error message associated to the current value of SQLCODE.
• If it is used outside an exception handler without a parameter, SQLERRM always returns a “normal, successful completion” message.
• The message returned begins with the Oracle error code.
• For user-defined exceptions, Oracle returns the message “user-defined exception”, unless you have specified your own message using the pragma EXCEPTION_INIT.
• If a value of 0 is used as the error_number, SQLERRM returns a “normal, successful completion” message.
• If a positive number other than 100 is used, then SQLERRM returns a “user-defined exception” message.
• If a value of 100 is used, SQLERRM returns “ORA-01403: no data found”.

 

How Should I Use the SQLCODE and SQLERRM Functions?

The best way to use the SQLCODE and SQLERRM functions is inside an exception handler in PL/SQL.

This is where you handle any errors that occur. If you use the SQLCODE function or SQLERRM function outside the error handler, it will return 0 and won’t be of use to you.

It’s also a good idea to assign the SQLCODE to a variable and then use that variable in your code.

The SQLERRM function is similar. It returns the error message associated with the most recent error. It is common practice to use both SQLCODE and SQLERRM together, as SQLERRM describes what the error is, but it’s up to you.

 

Why Am I Getting SQLERRM Invalid Identifier?

Sometimes, you might get an SQLERRM invalid identifier message in your SQL:

ORA-00904: : invalid identifier

This is often caused by a function being called that does not exist, or that needs the package to be recompiled, or the function is not accessible by the current user.

 

Can I Log the Line Number Using Oracle SQLERRM?

Yes, you can, but there’s no simple way to do it.

You can use the DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); function to output the line number, which looks something like this:

ORA-06512: at line 12

Then, to store it, you’ll need to perform some string manipulation on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.

 

What Is The SQLERRM Length?

The maximum length of the SQLERRM function return value is 512 characters.

This is useful if you want to store the value in a table when you log an error.

 

Oracle SQLCODE List

This page here shows a list of many SQLCODE values. The SQLCODE of 000 is the default value and indicates success, but other messages are also not failure messages, so you shouldn’t always test for when the message is 000.

 

Examples of the SQLCODE Function

Here are some examples of the Oracle SQLCODE function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – Output Error

This example PL/SQL code shows you how to output an error code.

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('An error was encountered: '||SQLCODE||' - '||SQLERRM);
END;

You can see that I have used the SQLCODE function as well. There is no parameter of the SQLERRM function, which means it uses the value of the SQLCODE function.

Example 2 – Raise Exception

This example PL/SQL code shows you how to raise an exception from the error that was found

EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20010, 'An error was encountered: '||SQLCODE||' - '||SQLERRM);
END;

Example 3 – Insert Into Table

This example PL/SQL code shows you how to insert the error message into a table for logging purposes. You would change the value of ‘thisFunction’ to the name of the function that this code is in.

EXCEPTION
  WHEN OTHERS THEN
    errorcode := SQLCODE;
    errormessage := SQLERRM;

    INSERT INTO error_log (error_number, error_message, function_name)
    VALUES (errorcode, errormessage, 'thisFunction');

END;

Example 4 – Output Specific Message

This example uses a parameter for the SQLERRM function and outputs the result.

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('An error was encountered: '||SQLERRM(-20010));
END;

 

You can find a full list of Oracle SQL functions here.