RAISE_APPLICATION_ERROR

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 an user-defined exception. 
b) to make the user-defined exception look like an Oracle error.

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 folowed 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.

Using the above example we can display a error message using RAISE_APPLICATION_ERROR.

DECLARE

  huge_sal EXCEPTION;

BEGIN

  FOR i IN

  (SELECT sal FROM emp )

  LOOP

    if I.SAL > 3000 then

      DBMS_OUTPUT.PUT_LINE(I.SAL);

    ELSE

      RAISE HUGE_SAL;

      end if;

    END LOOP;

  EXCEPTION

  WHEN HUGE_SAL THEN

    raise_application_error(-20133,'Huge Salaray exception raised');

  end;

  /