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:
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.