If you want to know specifically about Oracle errors and exceptions, use SQLCODE and SQLERRM. Let us see how these standard Oracle functions work with syntax and examples.

What are SQLCODE and SQLERRM in Oracle?

SQLCODE returns SQL error code once the exception is raised from your code. It returns number associated for that exception by Oracle. It is an ANSI-standard code similar to other relational database that uses SQL.

SQLERRM returns user understandable error message associated with that SQL Code.

Syntax and Example of SQLCODE and SQLERRM







Without error_number parameter

With error_number parameter

Real life example of SQLCODE and SQLERRM

Suppose you wrote a query to fetch employees with salary greater than $10,000. For data not found you can use WHEN NO_DATA_FOUND exception. But what happens if an unexpected error occurred in that query? Your code will fail, but you won’t know exactly what caused the failure. Using WHEN OTHERS EXCEPTION, SQLCODE and SQLERRM, you can find the exact reason.


To log or audit errors, below code can be used

Some things you should know

  • It is advisable to use SQLCODE and SQLERRM inside Exception clause. Using them outside Exception will return 0 as error code which will be normal, successful completion.
  • Use both SQLCODE and SQLERRM together instead of using them one at a time.
  • The maximum length of SQLERRM is 512 bytes/characters.
  • ORA- codes and SQLCODE may have different error codes.
  • Most use of SQLCODE and SQLERRM will be in WHEN OTHERS THEN exception.
  • SQLCODE is negative for all internally defined exceptions except for NO_DATA_FOUND, whose code is +100.
  • In user-defined exception, default value of SQLCODE will return 1 and SQLERRM will return User-Defined Exception. However, it can be overridden.

SQLERRM documentation by Oracle

SQLCODE documentation by Oracle

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.