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

SQLCODE

Example

EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'Error  - '||SQLCODE);
END;

SQLERRM

Syntax

SQLERRM (error_number)

error_number is an optional parameter but should be a valid oracle error number.

Example

Without error_number parameter

EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'Error  Code- '||SQLCODE||', Error message - '||SQLERRM);
END;

With error_number parameter

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM(-20001));
END;

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.

BEGIN
  SELECT emp.employee_id, emp.emp_name
  FROM employees emp
  WHERE emp.salary > 10000;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001,'Data not found for employee -'||emp.employee_number);
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002,'Other error-'||SQLCODE||'-'||SQLERRM);
END;

Auditing

To log or audit errors, below code can be used

EXCEPTION
  WHEN OTHERS THEN
    ls_errorcode := SQLCODE;
    ls_errormessage := SQLERRM;
    INSERT INTO audit_error_emp (error_number, error_message)
    VALUES (ls_errorcode, ls_errormessage);
END;

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.