Difference between RAISE and RAISE_APPLICATION_ERROR in Oracle

Error handling is an important aspect of any coding standard. In Oracle, we do that by raising an exception using RAISE or RAISE_APPLICATION_ERROR. Many experienced programmers may have used both in one way or the other. But if you are new to PL/SQL, you may wonder about the difference between the two.  It is inevitable that you understand the difference between RAISE and RAISE_APPLICATION_ERROR. So why Oracle has given two ways of raising an error instead of one? Let us find out.

What is RAISE_APPLICATION_ERROR?

RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an error with number and user understandable error message. This helps user to work on the resolution of error in most exclusive and correct way.

Raise_application_error shows application specific error message. You cannot re-raise it.

Read more about Raise_Application_Error here

For Example,
DECLARE
  ln_employee_num NUMBER;
  ls_name  employees.name%TYPE;
BEGIN 
 SELECT emp.employee_number, emp.name
 INTO ln_employee_num, ls_name
 FROM employees emp
 WHERE emp.employee_number = 1;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  RAISE_APPLICATION_ERROR (-20000,'Data not found 
                                   for employee number:'||ln_employee_num); 
 WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR (-20001,'Other error while 
                                   fetching data for 
                                   employee number:'||ln_employee_num
                                   ||'. Error is '||SQLERRM);
END;

What is RAISE?

RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-defined exception. It is also used to raise standard oracle exception. Without showing an actual message of exception, RAISE will just raise an exception and fails the execution of the code.

The advantage of RAISE is that it can be used to re-raise the same exception again.

For Example,
DECLARE
  my_error EXCEPTION;
BEGIN
  IF 1 < 2 THEN 
    RAISE my_error; 
  END IF; 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE my_error;
  WHEN my_error THEN
    RAISE_APPLICATION_ERROR (-20000,'Error Raised. Please resolve');   
END;

Oracle documentation of handling PL/SQL Errors

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.