raise_application_error in Oracle

Exception handling is most important in any programming and the same rule applies with Oracle. When a user need to raise a user-defined error from the application raise_application_error allows you to define a new error message with a new error code. raise_application_error is a procedure and a good way to show a meaningful error by the application.

DBMS_STANDARD is the belonging package for the procedure and therefore no need to qualify for reference too. Once call the  raise_application_error immediately stop running the remaining code and roll back uncommitted transactions in the current session.

raise_application_error allow you to raise custom message and error codes. The error code number should be between the -20000 to -20999 range. In another way, you can define 1000 new error codes using this procedure.

The syntax for pragma exception_init and raise_application_error in Oracle

-- Syntax
raise_application_error(
    error_number, 
    message 
    [, {TRUE | FALSE}]
);
--

Parameter 1: Error Number (Mandatory): Error code for identifying later or catching later in the exception.

Parameter 2: Error message (Mandatory): Custom Error message relevant to error code. Length should be below 2048 bytes.

Parameter 3: Boolean (Optional): Default FALSE

FALSE: Replace the error stack with a new error code defined in the 1^st parameter

TRUE: Put the error code on top of the stack trace.

If you want to add user define error in the above you must define user-defined error code by using the below syntax. You may know to define new exceptions use EXCEPTION_INIT pragma.

-- Syntax
PRAGMA EXCEPTION_INIT (exception_name, error_code)
--

Oracle Trigger raise_application_error

What happens once you call the procedure

1. Stop the running code of the current session and
2. Rollback all operations done for OUT and IN OUT parameters
3. Note the rollback does not happen for global data structures like the database objects. If you like to roll back everything, you must manually call the rollback operation.

Oracle raise_application_error Example

 First I would like to go for a basic example for simplicity.

--
DECLARE
nRecords integer := 578;

BEGIN
IF (nRecords <= 1000) THEN
Raise_Application_Error (-20998, 'Number of records not enough for continue.');
ELSE
--Run my program
NULL
END IF;
END;

--

In this example, I try to execute some program if it records over 1000. But in this case, there is less number of records. So I want to raise a meaningful full error message to use saying not enough records to continue the remaining. See the error message of our code.

Now I would like to add a procedure in Oracle with a raise_application_error example.

--
CREATE PROCEDURE complete_order (
  need_date_ DATE
) 
IS
BEGIN
  IF need_date_ < SYSDATE THEN                   -- explicitly raise new  user defined exception
    RAISE_APPLICATION_ERROR(-20001, 'Order completion date should future date.');
  END IF;
END;
/
 
DECLARE
  past_need_date_   EXCEPTION;                       -- declare our exception
  PRAGMA EXCEPTION_INIT (past_need_date_, -20001);  -- assign our error code with our exception
BEGIN
  complete_order (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'));   -- invoke procedure

EXCEPTION
  WHEN past_need_date_ THEN                         -- handle exception
--Handle exeption 
END;
/
--

--
ORA-20001: <strong>Order completion date should future date</strong>
--

In this example looking to complete some orders but the need date is the required date for orders if the need date is in the past then there should be an error. I would like to add a new error with code -20001 and going to catch an error in the code later. The catching position can be in several places because my complete_order procedure can be called from different places.

If you haven’t used any PRAGMA EXCEPTION_INIT code to define exceptions then you have to catch exceptions by using the OTHER keyword like below

--
EXCEPTION
  WHEN OTHER THEN                         -- handle exception
--Handle exeption
--

Example 3

This is a bit different example but used the same Raise_Application_Error procedure to handle the exception

--
DECLARE
    Looperror_ EXCEPTION;
    n NUMBER := 9;
BEGIN
    FOR i IN 1..n LOOP
        dbms_output.put.line(i);
        IF i=n THEN
            RAISE Looperror;
        END IF;
    END LOOP;
EXCEPTION
    WHEN Looperror THEN
        RAISE_APPLICATION_ERROR(-20615, 'loop having some errors');
END;
/
--

The output will be like below

1
2
3
4
5
6
7
8
9

You can’t add any other error code to the Raise_Application_Error procedure. Then there will be an error from Oracle saying arguments in the procedure are wrong.

Summary

PL/SQL Raise_Application_Error procedure for defining new user errors. You can add a new custom message from the application. This error will halt the run and rollback DML operation in the current session. You can have your error code between -20000 to -20999.

Thank you for reading the article.

Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-error-handling.html#GUID-59C4325C-9D2A-4D24-8381-79B676A508A8