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.

Was this tutorial helpful?