# 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](../course-plan.md) · Appendix entry: [RAISE_APPLICATION_ERROR](../appendix.md#raise_application_error) · Related: [EXCEPTION](exception.md), [PRAGMA EXCEPTION_INIT](pragma-exception_init.md), [FORMAT_ERROR_BACKTRACE](format_error_backtrace.md)

---

## 1. What It Is and When

`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](pragma-exception_init.md); 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.

## 2. Syntax

```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](savepoint.md)).

## 3. Examples on the Course Schema

### 3a. Validation

```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.

### 3b. Numbers in one place, names everywhere

```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.

### 3c. Translating a low-level error into a business error

```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`.

### 3d. keep_errors — what the stack looks like

```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.

### 3e. How a client sees it

```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.

### 3f. Not an error — return it instead

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

## 4. What Breaks at Scale

- 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](save-exceptions.md).

## 5. Pitfalls

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](savepoint.md)).
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.

## 6. Related

- [EXCEPTION](exception.md) — the handler side.
- [PRAGMA EXCEPTION_INIT](pragma-exception_init.md) — catching `-20xxx` by name.
- [FORMAT_ERROR_BACKTRACE](format_error_backtrace.md) — what survives on the stack, and where.
- [SAVEPOINT](savepoint.md) — undoing work after the error.