Deep Dive — RAISE_APPLICATION_ERROR

The way a business rule says no — with a number the client can switch on and a message a human can read.

Used in
Chapter 7 — Exceptions
Appendix entry
RAISE_APPLICATION_ERROR
Related
EXCEPTION PRAGMA EXCEPTION_INIT FORMAT_ERROR_BACKTRACE

RAISE_APPLICATION_ERROR(code, message) raises an exception with a user-chosen ORA number in the range -20000 .. -20999 and a message. It is how PL/SQL turns "this is not allowed" into something every caller understands: PL/SQL catches it by a name mapped with PRAGMA EXCEPTION_INIT; JDBC sees SQLException.getErrorCode() = 20010; SQL*Plus prints ORA-20010: <message>.

Use it for validation failures and business-state violations. Do not use it for expected outcomes ("no rows") that the caller will handle every time — that is a return value, not an error.

SQL 
RAISE_APPLICATION_ERROR(<error_number>, <message> [, <keep_errors>]);
  • <error_number> — integer from -20000 to -20999 (ORA-21000 if outside the range).
  • <message> — VARCHAR2, up to 2048 bytes; longer is truncated.
  • <keep_errors> — BOOLEAN, default FALSE. TRUE keeps the previous error on the error stack (so FORMAT_ERROR_STACK shows both); FALSE replaces it.
  • The procedure never returns. Inside a handler it replaces the current exception; to re-raise the current one use RAISE;.
  • It does not roll back anything by itself. Statement-level rollback applies to the failing SQL statement only (SAVEPOINT).

SQL 
CREATE OR REPLACE PROCEDURE place_order (p_customer_id NUMBER, p_amount NUMBER) IS
BEGIN
  IF p_amount IS NULL OR p_amount <= 0 THEN
    RAISE_APPLICATION_ERROR(-20010, 'amount must be positive, got ' || NVL(TO_CHAR(p_amount), 'NULL'));
  END IF;
  INSERT INTO orders (order_id, customer_id, order_date, amount, status)
  VALUES (orders_seq.NEXTVAL, p_customer_id, SYSDATE, p_amount, 'NEW');
END;
/

Put the value in the message. "amount must be positive" is a rule; "got -5" is a diagnosis.

SQL 
CREATE OR REPLACE PACKAGE app_errors AS
  -- 200xx: orders     201xx: customers     202xx: pricing
  c_bad_amount     CONSTANT PLS_INTEGER := -20010;
  c_order_missing  CONSTANT PLS_INTEGER := -20011;
  c_cust_inactive  CONSTANT PLS_INTEGER := -20101;

  e_bad_amount     EXCEPTION;  PRAGMA EXCEPTION_INIT(e_bad_amount,    -20010);
  e_order_missing  EXCEPTION;  PRAGMA EXCEPTION_INIT(e_order_missing, -20011);
  e_cust_inactive  EXCEPTION;  PRAGMA EXCEPTION_INIT(e_cust_inactive, -20101);
END;
/
-- raising
RAISE_APPLICATION_ERROR(app_errors.c_cust_inactive, 'customer ' || p_id || ' is ' || v_status);
-- catching
EXCEPTION
  WHEN app_errors.e_cust_inactive THEN ...

The pragma needs a literal, hence the duplication of the number in the constant and the pragma — keep them adjacent so they cannot drift.

SQL 
CREATE OR REPLACE PROCEDURE add_customer (p_id NUMBER, p_tier VARCHAR2) IS
BEGIN
  INSERT INTO customers (customer_id, status, tier) VALUES (p_id, 'NEW', p_tier);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20102, 'customer ' || p_id || ' already exists', TRUE);
END;
/

The caller gets ORA-20102 with a sentence instead of ORA-00001: unique constraint (APP.CUSTOMERS_PK) violated. With keep_errors => TRUE, the original ORA-00001 is still on the stack for anyone who logs FORMAT_ERROR_STACK.

SQL 
BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'inner');
  EXCEPTION
    WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20002, 'outer', TRUE);
  END;
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
-- ORA-20002: outer
-- ORA-20001: inner

Without TRUE, only ORA-20002: outer remains. SQLCODE/SQLERRM always report the top (latest) error.

Java 
try { cs.execute(); }
catch (SQLException e) {
  if (e.getErrorCode() == 20101) showMessage("Customer inactive: " + e.getMessage());
  else throw e;
}

getMessage() returns ORA-20101: customer 42 is CANCELLED\nORA-06512: at "APP.PLACE_ORDER", line 7 ... — strip the prefix and the stack in the UI layer, or keep the message text free of them by convention.

SQL 
-- questionable: forces every caller into a handler for a normal outcome
IF NOT l_tier.EXISTS(p_id) THEN RAISE_APPLICATION_ERROR(-20103, 'no tier'); END IF;

-- better for an expected outcome
RETURN CASE WHEN l_tier.EXISTS(p_id) THEN l_tier(p_id) ELSE 'NONE' END;

  • Raising costs: unwinding the stack, building the message, possibly the backtrace. Per row in a 500M loop it is significant; validate in SQL (a WHERE that excludes bad rows, a CHECK constraint with LOG ERRORS) rather than raising per row.
  • Message building ('customer ' || p_id || ...) is only executed on the error path — cheap. Building the message before the IF is not.
  • A raised -20xxx in a FORALL without SAVE EXCEPTIONS (e.g. from a trigger) aborts the whole batch — see SAVE EXCEPTIONS.

  1. ORA-21000: error number argument to raise_application_error of -1234 is out of range — only -20000..-20999.
  2. Reusing the same number for different meanings across packages — define ranges per module (3b).
  3. Raising inside a WHEN OTHERS without keep_errors => TRUE — the original error is lost; log it first or keep it.
  4. Expecting a rollback — it does not roll back the transaction. The handler that catches it decides (SAVEPOINT).
  5. Message > 2048 bytes — silently truncated. Put detail in the log table, not the message.
  6. Localized messages built in PL/SQL — the language of the message becomes the language of the log. Log codes; translate in the UI.
  7. Using it for flow control between procedures ("throw to signal done") — expensive and unreadable.