Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / Oracle RAISE_APPLICATION_ERROR

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}] );

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; /

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?
  • YesNo
Previous PL/SQL Raise Exceptions
Next PL/SQL Exception Propagation

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.