RAISE_APPLICATION_ERROR — How Generic Codes Cost $2.4M

RAISE_APPLICATION_ERROR is a built-in PL/SQL procedure that raises a user-defined exception with a custom error code and message. It exists because Oracle's default exception propagation produces ORA-06512 stack traces that are useful for database developers reading source code but useless for application developers, operations teams, and monitoring systems that need to understand what happened and how to respond.

The procedure accepts three parameters: an error number in the -20000 to -20999 range, a message string up to 2048 bytes, and an optional boolean that controls whether the custom error replaces or chains onto the existing error stack. When called, it terminates the current PL/SQL block immediately — no code after the call executes — and propagates the error to the caller. The caller can be another PL/SQL block, a JDBC application, a Python script, or a SQL*Plus session.

The deeper insight is that RAISE_APPLICATION_ERROR is not just an error-handling utility — it is a contract between the database layer and the application layer. A well-designed PL/SQL API uses custom error codes to communicate specific failure modes: -20001 for entity not found, -20002 for duplicate submission, -20050 for deadlock detected (retryable), -20101 for insufficient funds. The application maps each custom error code to a specific recovery action — display an error, retry with backoff, redirect to a different payment method, or escalate to operations. Without this contract, the application is forced to parse error message strings or treat every database error as a generic failure, which leads to blind retries, silent data corruption, and the kind of incident described below.

RAISE_APPLICATION_ERROR Syntax and Parameters

RAISE_APPLICATION_ERROR accepts three parameters. Only the first two are required. Each parameter has a strict constraint, and violating the constraint produces behavior that is easy to misdiagnose.

The first parameter, error_number, must be an integer in the range -20000 to -20999 inclusive. This range provides 1000 unique error codes for application-specific errors. Oracle reserves all other negative error numbers for its own internal errors. If you pass a number outside this range — say -30001 or -19999 — Oracle raises ORA-21000: error number argument to raise_application_error of -30001 is out of range. The RAISE_APPLICATION_ERROR call itself fails, and the caller sees ORA-21000 instead of your intended custom error. This is not a 'silent ignore' — it is a different error entirely, and it is confusing because the error message references raise_application_error rather than your business logic.

The second parameter, message, is a VARCHAR2 string up to 2048 bytes. However, most client tools — SQL*Plus, SQL Developer, JDBC's default SQLException.getMessage() — display roughly the first 512 bytes. The full message is accessible through the error stack APIs (DBMS_UTILITY.FORMAT_ERROR_STACK in PL/SQL, the full exception object in application languages). The practical rule is to front-load the most critical diagnostic information — error type, entity ID, invalid value, original SQLCODE — in the first 512 bytes.

The third parameter, keep_error_stack (commonly called 'stack'), is a BOOLEAN that defaults to FALSE. When FALSE, the custom error replaces the entire error stack — the original Oracle error, its line number, and any intermediate errors are discarded. When TRUE, the custom error is chained onto the top of the existing stack — the original error and all intermediate context are preserved. This parameter has the most impact on production debuggability. Using FALSE in a nested handler destroys the original error; using TRUE preserves it.

Error Number Strategy: Designing a Custom Error Code System

A well-designed error code system maps each code to a specific failure mode, a severity level, and a recovery action. Without a systematic approach, teams assign error codes ad hoc — typically starting at -20001 and incrementing — leading to collisions across modules, gaps in coverage, and code that is unmaintainable after the original developer leaves.

The recommended approach is to partition the -20000 to -20999 range into reserved blocks for each module or domain. For example: -20000 to -20099 for the orders module, -20100 to -20199 for payments, -20200 to -20299 for inventory, -20900 to -20999 reserved for infrastructure (deadlock, timeout, unexpected). Within each block, use a consistent sub-pattern: codes ending in 01-09 for 'not found' errors, 10-19 for 'duplicate' errors, 20-29 for 'invalid state' errors, 30-39 for 'business rule violation' errors. This convention makes error codes self-documenting — any engineer can look at -20112 and know it is a payments module duplicate error without checking a table.

Each error code should have a registered definition in a central error registry table. The registry stores the code, a short machine-readable name (DUPLICATE_ORDER), a human-readable description, a severity level (for alerting thresholds), and a recommended recovery action (for the application layer). This registry serves as the contract between the database team and the application team — changes to it must be coordinated and versioned.

The error message format should be consistent and parseable by both humans and automated log analysis tools. A proven format is: ERROR_NAME: key1=value1 key2=value2 | original=SQLERRM. For example: DUPLICATE_ORDER: order_id=12345 customer_id=67890 | original=ORA-00001: unique constraint (ORDERS_UK) violated. This format allows grep, structured log parsers, and monitoring systems to extract specific fields without fragile string parsing.

Stack Behavior: When to Use keep_error_stack=TRUE

The keep_error_stack parameter controls whether RAISE_APPLICATION_ERROR preserves or replaces the existing error stack. This single boolean has more impact on production debuggability than any other aspect of PL/SQL exception handling.

When keep_error_stack=FALSE (the default), the custom error replaces the entire error stack. The caller sees only the custom error code, the custom message, and the ORA-06512 line where RAISE_APPLICATION_ERROR was called. The original Oracle error — ORA-00001, ORA-01403, ORA-00060, whatever triggered the exception handler — is gone. The intermediate call chain is gone. The original line number where the error occurred is gone. For debugging, this means the only information available is what the developer chose to include in the custom message.

When keep_error_stack=TRUE, the custom error is chained onto the top of the existing stack. The caller sees the custom error code and message at the top, followed by the complete original error stack — the original Oracle error, every ORA-06512 line in the call chain, and any intermediate errors. This provides the full diagnostic context: what the business-level operation was (the custom error), what the database-level failure was (the original error), and exactly where it happened (the line numbers).

The practical rule is straightforward: use keep_error_stack=FALSE only at the outermost API boundary — the top-level procedure that the application calls directly — where you deliberately want to hide internal implementation details from the caller. Use keep_error_stack=TRUE everywhere else — in nested handlers, in utility procedures, in any exception handler that catches and re-raises. The default should be TRUE in your mental model, even though Oracle's default is FALSE.

The most common mistake is using FALSE in a nested handler inside a deep call chain. When an inner procedure raises ORA-00001 and an outer procedure catches it and re-raises with RAISE_APPLICATION_ERROR(-20010, 'Payment failed', FALSE), the original ORA-00001 — including which constraint was violated, which table, which columns — is permanently lost. The application team sees 'Payment failed' and has no way to determine the root cause without SSH access to the database server and the ability to reproduce the exact scenario.

WHEN OTHERS Anti-Pattern: The Silent Killer

WHEN OTHERS is the most dangerous exception handler in PL/SQL. It catches every exception — every Oracle error, every custom error, every unexpected condition — including errors the developer never anticipated and cannot safely handle. When combined with RAISE_APPLICATION_ERROR using a single generic error code and a vague message, it destroys the diagnostic value of the original error and makes the application's error-handling logic unreliable.

The anti-pattern takes many forms but the core pattern is always the same: a WHEN OTHERS handler catches all exceptions and re-raises with a generic code and message. RAISE_APPLICATION_ERROR(-20001, 'Something went wrong'). Every distinct failure mode — unique constraint violation, table not found, arithmetic overflow, deadlock, out of memory — is mapped to the same error code with the same useless message. The application cannot distinguish retryable errors (deadlock, lock timeout) from permanent errors (unique constraint, data integrity violation) from critical errors (out of memory, tablespace full). Blind retries on permanent errors cause data corruption. No retries on transient errors cause unnecessary failures.

The correct approach has three layers. First, catch specific named exceptions that you expect and can handle: DUP_VAL_ON_INDEX, NO_DATA_FOUND, TOO_MANY_ROWS. Each gets its own RAISE_APPLICATION_ERROR with a distinct code and context. Second, catch specific SQLCODE values using WHEN OTHERS with an IF/CASE on SQLCODE for errors that do not have named exceptions (e.g., ORA-00060 for deadlock). Third, the WHEN OTHERS fallback handler — which should exist as the last handler — must include the original SQLCODE, SQLERRM, and FORMAT_ERROR_BACKTRACE in the custom error message and must use keep_error_stack=TRUE. This ensures that even truly unexpected errors carry full diagnostic context.

An even better approach is a centralized exception handler procedure that maps SQLCODE to a custom error code using the error registry. This eliminates per-procedure exception handling boilerplate, ensures consistent message formatting, and guarantees that every error — expected or unexpected — is raised with proper context.

Application-Side Error Code Mapping

The database raises custom error codes through RAISE_APPLICATION_ERROR. The application maps those codes to recovery actions. This mapping is the contract that makes the entire system reliable — without it, the application treats every database error as a generic failure and either retries blindly or gives up prematurely.

In Java (JDBC), custom error codes are accessible via SQLException.getErrorCode(). The value matches the error_number parameter of RAISE_APPLICATION_ERROR: -20010 for DUPLICATE_ORDER, -20950 for DEADLOCK_DETECTED, etc. The application reads this integer and dispatches to the appropriate handler. In Python (python-oracledb, cx_Oracle), the code is available via exception.args[0].code. In .NET (ODP.NET), it is OracleException.Number. Every language provides a way to extract the integer code — use it.

The application should maintain an error code registry that mirrors the database registry. Each code maps to: a user-facing message (never the raw database message), a severity level (for monitoring and alerting thresholds), a retryable flag (boolean), a maximum retry count (for retryable errors), and a recovery action (display error, redirect, retry, escalate). The application never displays the raw database error to the end user — it translates the custom code to a user-friendly message using the registry.

For retryable errors (deadlock, lock timeout), the application retries with exponential backoff and a maximum retry count. For permanent errors (duplicate, invalid state), the application displays the appropriate user-facing message and does not retry. For critical errors (unexpected error, resource exhaustion), the application escalates to the operations team via alerting and displays a generic 'contact support' message to the user. Unregistered error codes — codes not in the registry — should default to the critical path and escalate, because an unknown error code means the contract is broken.

RAISE_APPLICATION_ERROR vs. RAISE vs. Implicit Propagation

Oracle provides three mechanisms for propagating errors from PL/SQL: RAISE (re-raise a named exception), RAISE_APPLICATION_ERROR (raise a custom error with a specific code and message), and implicit propagation (let an unhandled Oracle error propagate without any handler). Each produces a different error code, a different message, and a different stack trace for the caller. Choosing the right mechanism depends on the caller's needs.

RAISE is used to re-raise the current exception unchanged. When you catch an exception in a handler and call RAISE with no arguments, the original exception — its error code, its message, its entire stack trace — propagates to the caller exactly as if the handler did not exist. Use RAISE when you need to perform some action in the handler (logging, cleanup, setting a flag) but do not need to customize the error for the caller.

RAISE_APPLICATION_ERROR is used when the caller needs a custom error code to determine the recovery action. It replaces the current error (unless keep_error_stack=TRUE) with a code in the -20000 to -20999 range and a descriptive message. Use this at API boundaries — the procedures that the application calls directly — where the caller needs to distinguish between different failure modes.

Implicit propagation happens when a PL/SQL block has no exception handler for the raised error, or no exception handler at all. The Oracle error propagates as-is — ORA-00001 with its constraint name, ORA-01403 with its context — through the call stack. Use implicit propagation for internal procedures that are called only by other PL/SQL code and do not need to customize the error.

The decision tree: Is the caller an application that needs a custom error code? Use RAISE_APPLICATION_ERROR. Do you need to log or clean up but not customize the error? Use RAISE. Is the caller another PL/SQL procedure that can handle the raw Oracle error? Use implicit propagation (no handler).

Frequently Asked Questions