Oracle RAISE_APPLICATION_ERROR

Summary: in this tutorial, you will learn how to use the procedure raise_application_error to issue user-defined error messages.

Introduction to raise_application_error procedure

The procedure raise_application_error allows you to issue an user-defined error from a code block or stored program.

By using this procedure, you can report errors to the callers instead of returning unhandled exceptions.

The raise_application_error has the following syntax:

raise_application_error(
    error_number, 
    message 
    [, {TRUE | FALSE}]
);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The error_number is a negative integer with the range from -20999 to -20000.
• The message is a character string that represents the error message. Its length is up to 2048 bytes.
• If the third parameter is FALSE, the error replaces all previous errors. If it is TRUE, the error is added to the stack of previous errors.

The raise_application_error belongs to the package DBMS_STANDARD, therefore, you do not need to qualify references to it.

When the procedure raise_application_error executes, Oracle halts the execution of the current block immediately. It also reverses all changes made to the OUT or IN OUT parameters.

Note that the changes made to the global data structure such as packaged variables, and database objects like tables will not be rolled back. Therefore, you must explicitly execute the ROLLBACK statement to reverse the effect of the DML.

Oracle raise_application_error example

Let’s take a look at some examples of using the raise_application_error procedure to raise exceptions.

This example uses the raise_application_error procedure to raise an exception with id -20111 and message 'Credit Limit Exceeded':

DECLARE
    credit_limit_exceed EXCEPTION;
    PRAGMA exception_init(credit_limit_exceed, -20111);

    l_customer_id     customers.customer_id%TYPE := &customer_id;
    l_credit_limit    customers.credit_limit%TYPE := &credit_limit;

    l_customer_credit customers.credit_limit%TYPE;
    
BEGIN
    -- get customer credit limit
    SELECT credit_limit INTO l_customer_credit
    FROM customers
    WHERE customer_id = l_customer_id;
    
    -- raise an exception if the credit limit is exceeded
    IF l_customer_credit > l_credit_limit THEN
        raise_application_error(-20111,'Credit Limit Exceeded');
    END IF;
    
    dbms_output.put_line('Credit Limit is checked and passed');
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer with id ' || l_customer_id || ' does not exist.');
END;
/
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, declare a user-defined exception credit_limit_exceed associated with the error number -20111.
• Second, declare two variables l_customer_id and l_credit_limit to store customer id and credit limit entered by users.
• Third, get the customer credit limit based on the customer id.
• Finally, check the input credit with the customer credit and use the raise_application_error to raise an exception.

In this tutorial, you have learned how to use the Oracle raise_application_error procedure to raise an exception.