ORA-20000: User-Defined Exception - RAISE_APPLICATION_ERROR Guide

ORA-20000: User-Defined Exception

Error Overview

Error Text: ORA-20000: [custom message text]

This error is raised by PL/SQL code using the RAISE_APPLICATION_ERROR procedure. Unlike Oracle system errors, ORA-20000 is a user-defined exception — the error number -20000 is the default when developers use RAISE_APPLICATION_ERROR without choosing a more specific code. Oracle reserves the range -20000 to -20999 exclusively for application-defined errors, giving developers 1,000 custom error codes for business logic validation, data integrity checks, and custom error handling in stored procedures, triggers, and packages.

Understanding RAISE_APPLICATION_ERROR

The -20000 to -20999 Range

Oracle User-Defined Error Range
├── -20000  Default / most commonly used
├── -20001  Often used for validation errors
├── -20002  Often used for business rule violations
├── ...
├── -20500  Mid-range (application-specific)
├── ...
└── -20999  End of user-defined range

Syntax

RAISE_APPLICATION_ERROR(error_number, error_message [, keep_errors]);
-- error_number: Must be between -20000 and -20999
-- error_message: Up to 2048 bytes (truncated if longer)
-- keep_errors: TRUE to preserve error stack, FALSE (default) to replace it

Common Scenarios

• Application validation failures — Business rules enforced in PL/SQL triggers or procedures
• Custom error handling in triggers — BEFORE INSERT/UPDATE triggers rejecting invalid data
• Stored procedure guard clauses — Parameter validation at the start of procedures
• Default error code overuse — Developers using -20000 for all custom errors without differentiation
• Third-party application errors — Packaged applications raising user-defined exceptions
• API layer validation — Database-level APIs enforcing contract rules

Diagnostic Steps

1. Read the Custom Error Message

-- The most important diagnostic step: the error message itself tells you what happened.
-- ORA-20000 errors always include a custom message set by the developer.
-- Example error stack:
--
-- ORA-20000: Employee salary cannot exceed department budget
-- ORA-06512: at "HR.PKG_SALARY_MANAGEMENT", line 45
-- ORA-06512: at "HR.TRG_SALARY_CHECK", line 12
-- ORA-06512: at line 1

-- The ORA-06512 lines show the exact PL/SQL call stack:
--   Package HR.PKG_SALARY_MANAGEMENT, line 45 raised the error
--   Called from trigger HR.TRG_SALARY_CHECK, line 12

2. Find the PL/SQL Source Code

-- Search for RAISE_APPLICATION_ERROR in a specific object
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE owner = 'HR'  -- Replace with schema from ORA-06512 stack
  AND name = 'PKG_SALARY_MANAGEMENT'  -- Replace with object from ORA-06512 stack
  AND UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%'
ORDER BY line;

-- Search across all objects in a schema for a specific error code
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE owner = 'HR'
  AND UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%(-20000%'
ORDER BY name, line;

-- Search for the exact error message text to find where it originates
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE owner = 'HR'
  AND UPPER(text) LIKE '%SALARY CANNOT EXCEED%'  -- Use keywords from the error message
ORDER BY name, line;

3. Search All Schemas for the Error

-- Find all RAISE_APPLICATION_ERROR calls using -20000 across the database
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%(-20000%'
  AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'APPQOSSYS')
ORDER BY owner, name, line;

-- Find all user-defined error codes in use across the database
SELECT
  owner,
  name,
  type,
  REGEXP_SUBSTR(text, '-2[0-9]{4}') as error_code,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE REGEXP_LIKE(text, 'RAISE_APPLICATION_ERROR\s*\(\s*-2[0-9]{4}')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY error_code, owner, name, line;

4. Investigate Triggers on the Affected Table

-- List all triggers on a table (common source of ORA-20000)
SELECT
  owner,
  trigger_name,
  trigger_type,
  triggering_event,
  status,
  description
FROM dba_triggers
WHERE table_name = 'EMPLOYEES'  -- Replace with your table
  AND table_owner = 'HR'  -- Replace with your schema
ORDER BY trigger_type, trigger_name;

-- View trigger source code
SELECT
  owner,
  trigger_name,
  line,
  TRIM(text) as source_line
FROM dba_source
WHERE owner = 'HR'
  AND name = 'TRG_SALARY_CHECK'  -- Replace with trigger name from stack
  AND type = 'TRIGGER'
ORDER BY line;

-- Find all triggers that raise application errors
SELECT DISTINCT
  t.owner,
  t.trigger_name,
  t.table_name,
  t.triggering_event,
  t.status
FROM dba_triggers t, dba_source s
WHERE t.owner = s.owner
  AND t.trigger_name = s.name
  AND s.type = 'TRIGGER'
  AND UPPER(s.text) LIKE '%RAISE_APPLICATION_ERROR%'
  AND t.table_owner = 'HR'  -- Replace with your schema
ORDER BY t.table_name, t.trigger_name;

5. Check the Error Stack for Context

-- In PL/SQL, capture the full error stack
DECLARE
  v_error_msg VARCHAR2(4000);
  v_error_stack VARCHAR2(4000);
  v_call_stack VARCHAR2(4000);
BEGIN
  -- Your operation that might raise ORA-20000
  some_procedure();
EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := SQLERRM;
    v_error_stack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    v_call_stack := DBMS_UTILITY.FORMAT_CALL_STACK;

    DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
    DBMS_OUTPUT.PUT_LINE('Backtrace: ' || v_error_stack);
    DBMS_OUTPUT.PUT_LINE('Call Stack: ' || v_call_stack);
    RAISE;  -- Re-raise after logging
END;
/

-- Check recent errors in the alert log (requires DBA access)
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-20000%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY originating_timestamp DESC;

Immediate Solutions

Solution 1: Understand and Address the Business Rule

-- The ORA-20000 message tells you WHY the operation was rejected.
-- Example: "ORA-20000: Employee salary cannot exceed department budget"

-- Step 1: Check the condition the code is enforcing
SELECT
  e.employee_id,
  e.salary as proposed_salary,
  d.budget as department_budget,
  d.budget - SUM(e2.salary) as remaining_budget
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employees e2 ON e2.department_id = d.department_id
WHERE e.employee_id = 100  -- The employee you're trying to update
GROUP BY e.employee_id, e.salary, d.budget;

-- Step 2: Adjust your data to comply with the business rule
UPDATE employees
SET salary = 50000  -- Value that satisfies the constraint
WHERE employee_id = 100;

Solution 2: Temporarily Disable the Trigger (Emergency Only)

-- CAUTION: Only do this if you understand the consequences
-- and have confirmed the data change is valid

-- Disable the trigger
ALTER TRIGGER hr.trg_salary_check DISABLE;

-- Perform your operation
UPDATE employees SET salary = 150000 WHERE employee_id = 100;
COMMIT;

-- IMMEDIATELY re-enable the trigger
ALTER TRIGGER hr.trg_salary_check ENABLE;

-- Verify trigger is active
SELECT trigger_name, status
FROM dba_triggers
WHERE trigger_name = 'TRG_SALARY_CHECK' AND owner = 'HR';

Solution 3: Investigate and Fix the PL/SQL Logic

-- If the error is being raised incorrectly, examine the full source
SELECT line, TRIM(text) as source_code
FROM dba_source
WHERE owner = 'HR'
  AND name = 'PKG_SALARY_MANAGEMENT'
  AND type = 'PACKAGE BODY'
ORDER BY line;

-- Check the logic around the RAISE_APPLICATION_ERROR call
-- Look for the IF condition that leads to the error
-- Common issues:
--   - Wrong comparison operator (> vs >=)
--   - NULL handling (NVL/COALESCE missing)
--   - Data type mismatch in comparisons
--   - Stale cached values not reflecting current data

Long-Term Solutions

1. Implement a Custom Error Code Framework

Define Error Codes Systematically

-- Create an error code registry table
CREATE TABLE app_error_codes (
  error_code    NUMBER NOT NULL,
  error_name    VARCHAR2(100) NOT NULL,
  error_module  VARCHAR2(100),
  description   VARCHAR2(500),
  severity      VARCHAR2(20) DEFAULT 'ERROR',
  created_date  DATE DEFAULT SYSDATE,
  CONSTRAINT pk_app_error_codes PRIMARY KEY (error_code),
  CONSTRAINT chk_error_code_range CHECK (error_code BETWEEN -20999 AND -20000)
);

-- Register your application's error codes
INSERT INTO app_error_codes VALUES (-20000, 'GENERIC_ERROR', 'CORE', 'Generic application error - avoid using', 'ERROR', SYSDATE);
INSERT INTO app_error_codes VALUES (-20001, 'VALIDATION_ERROR', 'CORE', 'Input validation failure', 'ERROR', SYSDATE);
INSERT INTO app_error_codes VALUES (-20010, 'SALARY_EXCEEDS_BUDGET', 'HR', 'Salary exceeds department budget', 'ERROR', SYSDATE);
INSERT INTO app_error_codes VALUES (-20011, 'INVALID_HIRE_DATE', 'HR', 'Hire date cannot be in the future', 'ERROR', SYSDATE);
INSERT INTO app_error_codes VALUES (-20100, 'INSUFFICIENT_INVENTORY', 'SALES', 'Not enough inventory for order', 'ERROR', SYSDATE);
INSERT INTO app_error_codes VALUES (-20101, 'ORDER_ALREADY_SHIPPED', 'SALES', 'Cannot modify a shipped order', 'ERROR', SYSDATE);
COMMIT;

-- Create a package for consistent error raising
CREATE OR REPLACE PACKAGE app_errors AS
  -- HR Module: -20010 to -20099
  ec_salary_exceeds_budget  CONSTANT NUMBER := -20010;
  ec_invalid_hire_date      CONSTANT NUMBER := -20011;
  ec_employee_not_found     CONSTANT NUMBER := -20012;

  -- Sales Module: -20100 to -20199
  ec_insufficient_inventory CONSTANT NUMBER := -20100;
  ec_order_already_shipped  CONSTANT NUMBER := -20101;

  -- Raise error with consistent formatting
  PROCEDURE raise_error(
    p_error_code IN NUMBER,
    p_context    IN VARCHAR2 DEFAULT NULL
  );
END app_errors;
/

CREATE OR REPLACE PACKAGE BODY app_errors AS
  PROCEDURE raise_error(
    p_error_code IN NUMBER,
    p_context    IN VARCHAR2 DEFAULT NULL
  ) AS
    v_description VARCHAR2(500);
    v_message     VARCHAR2(2048);
  BEGIN
    -- Look up the error description
    BEGIN
      SELECT description INTO v_description
      FROM app_error_codes
      WHERE error_code = p_error_code;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_description := 'Unknown application error';
    END;

    -- Build the message with optional context
    v_message := v_description;
    IF p_context IS NOT NULL THEN
      v_message := v_message || ' [' || p_context || ']';
    END IF;

    RAISE_APPLICATION_ERROR(p_error_code, v_message, TRUE);
  END raise_error;
END app_errors;
/

Use the Framework Consistently

-- Instead of:
-- RAISE_APPLICATION_ERROR(-20000, 'Salary too high');

-- Use:
CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
  v_budget departments.budget%TYPE;
BEGIN
  SELECT budget INTO v_budget
  FROM departments
  WHERE department_id = :NEW.department_id;

  IF :NEW.salary > v_budget THEN
    app_errors.raise_error(
      app_errors.ec_salary_exceeds_budget,
      'Employee ' || :NEW.employee_id ||
      ', Salary: ' || :NEW.salary ||
      ', Budget: ' || v_budget
    );
  END IF;
END;
/

2. Implement Custom Error Logging

-- Create error log table
CREATE TABLE app_error_log (
  log_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  error_code    NUMBER,
  error_message VARCHAR2(2048),
  error_stack   VARCHAR2(4000),
  call_stack    VARCHAR2(4000),
  username      VARCHAR2(128) DEFAULT USER,
  module        VARCHAR2(100),
  action        VARCHAR2(100),
  client_info   VARCHAR2(100),
  log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Create logging procedure (autonomous transaction so it persists on rollback)
CREATE OR REPLACE PROCEDURE log_app_error(
  p_error_code    IN NUMBER DEFAULT SQLCODE,
  p_error_message IN VARCHAR2 DEFAULT SQLERRM
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_error_log (
    error_code, error_message, error_stack, call_stack,
    module, action, client_info
  ) VALUES (
    p_error_code,
    p_error_message,
    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
    DBMS_UTILITY.FORMAT_CALL_STACK,
    SYS_CONTEXT('USERENV', 'MODULE'),
    SYS_CONTEXT('USERENV', 'ACTION'),
    SYS_CONTEXT('USERENV', 'CLIENT_INFO')
  );
  COMMIT;
END;
/

-- Query recent application errors
SELECT
  log_id,
  error_code,
  error_message,
  username,
  module,
  TO_CHAR(log_timestamp, 'YYYY-MM-DD HH24:MI:SS') as error_time
FROM app_error_log
WHERE error_code BETWEEN -20999 AND -20000
  AND log_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY log_timestamp DESC;

3. Application-Level Exception Handling

// Java example: handling ORA-20000 range errors
public class OracleAppErrorHandler {

    public void executeOperation(String sql, Object... params) {
        try {
            // Execute the operation
            jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            Throwable rootCause = e.getRootCause();
            if (rootCause instanceof SQLException sqlEx) {
                int errorCode = sqlEx.getErrorCode();

                // Check if it's a user-defined error (-20000 to -20999)
                if (errorCode >= 20000 && errorCode <= 20999) {
                    String message = sqlEx.getMessage();
                    // Strip the "ORA-20xxx: " prefix to get the clean message
                    String cleanMessage = message.replaceFirst("ORA-\\d+:\\s*", "");

                    // Map to application exception based on error code
                    switch (errorCode) {
                        case 20010:
                            throw new BudgetExceededException(cleanMessage);
                        case 20100:
                            throw new InsufficientInventoryException(cleanMessage);
                        case 20101:
                            throw new OrderModificationException(cleanMessage);
                        default:
                            throw new ApplicationValidationException(cleanMessage);
                    }
                }
            }
            throw e; // Re-throw non-application errors
        }
    }
}

Oracle 23ai Enhancements

Oracle 23ai introduces improvements relevant to user-defined exceptions:

-- Oracle 23ai: Improved error messages with DBMS_UTILITY
-- FORMAT_ERROR_BACKTRACE now provides more detail, including
-- the exact subprogram name in packages.

-- Oracle 23ai also supports longer identifiers (128 bytes),
-- allowing more descriptive procedure and package names
-- in the error stack.

-- New in 23ai: EXCEPTION_INIT with named constants is cleaner
DECLARE
  e_salary_exceeded EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_salary_exceeded, -20010);
BEGIN
  -- Your code
  NULL;
EXCEPTION
  WHEN e_salary_exceeded THEN
    -- Handle specifically
    DBMS_OUTPUT.PUT_LINE('Salary validation failed: ' || SQLERRM);
END;
/

Prevention Strategies

1. Error Code Documentation Standards

-- Document all error codes in your application
-- Maintain a central registry (table or package spec)

-- Package specification serves as documentation
CREATE OR REPLACE PACKAGE error_codes AS
  /*
  || Error Code Registry for MyApplication
  || Range allocation:
  ||   -20000       : Reserved (do not use - too generic)
  ||   -20001 - 009 : Core framework errors
  ||   -20010 - 099 : HR module errors
  ||   -20100 - 199 : Sales module errors
  ||   -20200 - 299 : Inventory module errors
  ||   -20900 - 999 : Reserved for future use
  */

  -- Core
  ec_generic_validation CONSTANT NUMBER := -20001;
  ec_record_not_found   CONSTANT NUMBER := -20002;
  ec_concurrent_update  CONSTANT NUMBER := -20003;

  -- HR
  ec_salary_exceeds_budget   CONSTANT NUMBER := -20010;
  ec_invalid_hire_date       CONSTANT NUMBER := -20011;

  -- Sales
  ec_insufficient_inventory  CONSTANT NUMBER := -20100;
  ec_order_already_shipped   CONSTANT NUMBER := -20101;
END error_codes;
/

2. Proper Exception Handling Patterns

-- GOOD: Specific error code with meaningful message and context
RAISE_APPLICATION_ERROR(
  -20010,
  'Salary ' || v_salary || ' exceeds department budget of ' || v_budget ||
  ' for department ' || v_dept_id
);

-- BAD: Generic error code with vague message
RAISE_APPLICATION_ERROR(-20000, 'Invalid data');

-- GOOD: Preserve the error stack with third parameter
RAISE_APPLICATION_ERROR(-20010, 'Salary validation failed', TRUE);

-- GOOD: Log before raising
log_app_error(-20010, 'Salary check failed for emp ' || v_emp_id);
RAISE_APPLICATION_ERROR(-20010, 'Salary exceeds budget');

3. Testing User-Defined Exceptions

-- Create test cases for your custom exceptions
DECLARE
  v_expected_error BOOLEAN := FALSE;
BEGIN
  -- Test that the validation fires correctly
  BEGIN
    UPDATE employees SET salary = 99999999 WHERE employee_id = 100;
    -- If we get here, the trigger didn't fire
    DBMS_OUTPUT.PUT_LINE('FAIL: Expected ORA-20010 but no error raised');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -20010 THEN
        DBMS_OUTPUT.PUT_LINE('PASS: ORA-20010 raised as expected');
        DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
        v_expected_error := TRUE;
      ELSE
        DBMS_OUTPUT.PUT_LINE('FAIL: Wrong error - got ' || SQLCODE || ': ' || SQLERRM);
      END IF;
  END;

  ROLLBACK;
END;
/

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose and resolve this error:

• dprivall.sql — Complete privilege check
• duser.sql — User information analysis
• dinvalid.sql — Find invalid database objects

Related Errors

• ORA-06512 - At line (PL/SQL error stack) — always accompanies ORA-20000
• ORA-06508 - PL/SQL could not find program unit
• ORA-04091 - Table is mutating (trigger context)
• ORA-01403 - No data found (often caught and re-raised as ORA-20xxx)
• ORA-00001 - Unique constraint violated (sometimes wrapped in ORA-20xxx)

Quick Reference

Emergency Response Steps

1. Read the full error message — it contains the developer’s explanation
2. Check the ORA-06512 stack to identify the exact PL/SQL object and line
3. Query DBA_SOURCE to view the code at the identified location
4. Understand the business rule being enforced
5. Fix the data or operation to comply with the rule, or fix the PL/SQL logic if incorrect
6. If urgent, consider temporarily disabling the trigger (with immediate re-enable plan)

Quick Commands

-- Find where the error is raised
SELECT owner, name, type, line, TRIM(text)
FROM dba_source
WHERE UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%(-20000%'
  AND owner NOT IN ('SYS', 'SYSTEM');

-- List triggers on a table
SELECT trigger_name, trigger_type, triggering_event, status
FROM dba_triggers
WHERE table_name = 'YOUR_TABLE' AND table_owner = 'YOUR_SCHEMA';

-- View trigger/procedure source
SELECT line, text FROM dba_source
WHERE owner = 'SCHEMA' AND name = 'OBJECT_NAME' ORDER BY line;

-- Capture full error stack in PL/SQL
BEGIN
  your_operation();
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;
/

-- Search for all custom error codes in the database
SELECT DISTINCT REGEXP_SUBSTR(text, '-2[0-9]{4}') as code, owner, name
FROM dba_source
WHERE REGEXP_LIKE(text, 'RAISE_APPLICATION_ERROR')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY code;

Prevention Guidelines

• Never use -20000 as a catch-all — Allocate specific codes per module
• Include context in error messages — Add relevant IDs, values, and conditions
• Document all custom error codes — Maintain a central registry
• Use the third parameter (TRUE) — Preserve the error stack for debugging
• Log before raising — Use autonomous transactions to persist error details
• Test exception paths — Write test cases that verify custom errors fire correctly