SQLCODE and SQLERRM in Oracle

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

SQLCODE

Syntax

Example

SQLERRM

Syntax

Example

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.

Auditing

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.

Leave a Reply

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