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
EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE); END;
SQLERRM (error_number) error_number is an optional parameter but should be a valid oracle error number.
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;
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.
Hope you like the article and find it useful. We highly appreciate comments and feedback.
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 email@example.com. Again I appreciate your visit. Hope to see you again and again!