Oracle PL/SQL: Raise_Application_Error

What is raise_application_error?

The raise_application_error is a built-in procedure that allows the developer to associate his/her own error message to an oracle error number. It helps you in returning a custom error to your application so that you can avoid returning unhandled exceptions. Thus, the message given by a developer acts as an oracle error message here (Interesting!).
When raise_application_error raises an exception all uncommitted transactions are rolled back and an error message with an error number (ORA-) is returned to the user. This built-in procedure can raise an exception but cannot handle it.
raise_application_error procedure is a part of DBMS_STANDARD and STANDARD packages. You don’t have to reference them while using it.

Syntax of raise_application_error

raise_application_error(error_number, error_message [, {TRUE | FALSE}]);

error_number: Range of negative integers between -20,000 and -20,999
error_message: User-defined error message of datatype varchar2(2000)
TRUE/FALSE: Optional parameter that tells the procedure to add an error to error stack.
If TRUE then error is added into the stack of previous errors, if FALSE then error replaces all previous errors.

Example

SQL> create or replace procedure calc_absense (v_absense IN number) as
 2
 3 begin
 4 if v_absence > 10 then
 5 raise_application_error(-20001, 'Employee absence cannot be more than 10');
 6 end if;
 7
 8 end;

Procedure created.

SQL> declare
 2 v_abs number := &1;
 2 begin
 3 calc_absense (v_abs);
 4 end;
 6 /

Enter value for number: 20
 old 2: v_abs number := &1;
 new 2: v_abs number := 20;
 declare
 *
 ERROR at line 1:
 ORA-20001: Employee absence cannot be more than 10
 ORA-06512: at "PUBS.calc_absense ", line 5
 ORA-06512: at line 3

Note: Error numbers other than between -20000 and -20999 are reserved by Oracle to display its own standard error messages.

If you liked the above post, please leave your comments below.