ORACLE BUILT-IN PROCEDURE

How RAISE_APPLICATION_ERROR ( ) used in ORACLE? How we define Custom Error messages in ORACLE?

RAISE_APPLICATION_ERROR ( ) allows users to create custom error messages. 

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it. RAISE_APPLICATION_ERROR is used for the following reasons,
a) To create a unique id for a user-defined exception.
b) To make the user-defined exception look like an Oracle error.

C) Used to replace generic Oracle exception messages with our own, more meaningful messages.

d) Used to create exception conditions of our own, when Oracle would not throw them.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (Error_Number, Error_Message);

• The Error number must be between -20000 and -20999 
• The Error_message is the message you want to display when the error occurs.

Steps to be followed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.

Let’s use this and see how it works-

SQL> SELECT * FROM EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

Let’s define an Exception which will check that salary inserted should not be less than the minimum limit set. Bind the same rule using the RAISE_APPLICATION_ERROR

DECLARE

     Low_Sal_Exc EXCEPTION;

     Min_sal NUMBER:= 8000;

     New_Sal NUMBER:= 4700;

    BEGIN

     INSERT INTO EMP_TEMP(ID, NAME, DEPTNO, SALARY)

                      VALUES (108,'Tom',30,New_Sal);

     IF New_Sal < Min_Sal THEN

        RAISE Low_Sal_Exc;

    END IF;

    DBMS_OUTPUT.PUT_LINE ('Record Inserted Successfully');

    Commit;

   EXCEPTION

   WHEN Low_Sal_Exc THEN

      Rollback;

      RAISE_APPLICATION_ERROR (-20102, 'Salary is Less than '||Min_Sal);

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

SQL> DECLARE
  2       Low_Sal_Exc EXCEPTION;
  3       Min_sal NUMBER:= 8000;
  4       New_Sal NUMBER:= 4700;
  5      BEGIN
  6       INSERT INTO EMP_TEMP(ID, NAME, DEPTNO, SALARY)
  7                        VALUES (108,'Tom',30,New_Sal);
  8
  9       IF New_Sal < Min_Sal THEN
 10          RAISE Low_Sal_Exc;
 11      END IF;
 12      DBMS_OUTPUT.PUT_LINE ('Record Inserted Successfully');
 13
 14      Commit;
 15     EXCEPTION
 16     WHEN Low_Sal_Exc THEN
 17        Rollback;
 18        RAISE_APPLICATION_ERROR (-20102, 'Salary is Less than '||Min_Sal);
 19     WHEN OTHERS THEN
 20        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 21  END;
 22  /
DECLARE
*
ERROR at line 1:
ORA-20102: Salary is Less than 8000
ORA-06512: at line 18

Let’s take another Example – Where we will fetch data from below table with DEPTNO and if no records found with that DEPTNO then Exception will be raised using RAISE_APPLICATION_ERROR.

DECLARE

   L_DEPTID NUMBER := 40;

   L_ENAME VARCHAR2(20);

   L_SAL NUMBER;

CURSOR C IS

   SELECT NAME, SALARY   FROM EMP_TEMP  

   WHERE DEPTNO = L_DEPTID;

BEGIN

   DBMS_OUTPUT.PUT_LINE('Opening Cursor');

   OPEN C;

   FETCH C INTO L_ENAME, L_SAL;

   IF C%ROWCOUNT =0 THEN

      RAISE_APPLICATION_ERROR(-20001,' No Employees in the Department :'||L_DEPTID);

   END IF;

   CLOSE C;

   DBMS_OUTPUT.PUT_LINE('Closing Cursor');

END;

SQL> DECLARE
  2     L_DEPTID NUMBER := 40;
  3     L_ENAME VARCHAR2(20);
  4     L_SAL NUMBER;
  5  CURSOR C IS
  6     SELECT NAME, SALARY   FROM EMP_TEMP
  7     WHERE DEPTNO = L_DEPTID;
  8  BEGIN
  9     DBMS_OUTPUT.PUT_LINE('Opening Cursor');
 10     OPEN C;
 11     FETCH C INTO L_ENAME, L_SAL;
 12     IF C%ROWCOUNT =0 THEN
 13        RAISE_APPLICATION_ERROR(-20001,' No Employees in the Department :'||L_DEPTID);
 14     END IF;
 15     CLOSE C;
 16     DBMS_OUTPUT.PUT_LINE('Closing Cursor');
 17  END;
 18  /
Opening Cursor
DECLARE
*
ERROR at line 1:
ORA-20001:  No Employees in the Department :40
ORA-06512: at line 13

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.