ORA-20001 — One Code Masked $340K Duplicate Refunds

-- Valid: error code in the reserved range
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'ORDER_NOT_FOUND: order_id=12345');
END;
/
-- Caller sees: ORA-20001: ORDER_NOT_FOUND: order_id=12345
-- The custom code and message are preserved correctly

-- INVALID: error code outside the range — raises ORA-21000
BEGIN
  RAISE_APPLICATION_ERROR(-30001, 'My custom error');
END;
/
-- Caller sees: ORA-21000: error number argument to raise_application_error
--              of -30001 is out of range
-- The custom code -30001 and message 'My custom error' are LOST

-- INVALID: positive error number — raises ORA-21000
BEGIN
  RAISE_APPLICATION_ERROR(20001, 'My custom error');
END;
/
-- Same result: ORA-21000 — positive numbers are out of range

-- Range allocation example:
-- Module allocation (100 codes per module):
--   -20000 to -20099: Orders module
--   -20100 to -20199: Payments module
--   -20200 to -20299: Inventory module
--   -20300 to -20399: Shipping module
--   -20400 to -20499: Customer module
--   -20900 to -20999: Infrastructure / shared / retryable errors
--
-- Sub-range convention within each module:
--   XX01 to XX09: Not-found errors
--   XX10 to XX19: Duplicate errors
--   XX20 to XX29: Invalid state errors
--   XX30 to XX39: Business rule violations
--   XX40 to XX49: External system / integration errors
--   XX50 to XX59: Retryable / transient errors
--   XX99:         Unexpected / catch-all error for the module

-- Diagnostic: find ALL RAISE_APPLICATION_ERROR calls in the codebase
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) AS source_line
FROM dba_source
WHERE UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%'
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, name, line;
-- Review each call: verify the code is in range and the message
-- includes diagnostic context (entity IDs, original SQLCODE)

-- Diagnostic: find error codes that may be outside the valid range
-- This regex extracts the first numeric argument to RAISE_APPLICATION_ERROR
SELECT
  owner,
  name,
  type,
  line,
  REGEXP_SUBSTR(text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1) AS error_code,
  TRIM(text) AS source_line
FROM dba_source
WHERE UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%'
  AND owner NOT IN ('SYS', 'SYSTEM')
  AND REGEXP_SUBSTR(text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1) IS NOT NULL
  AND NOT REGEXP_LIKE(
    REGEXP_SUBSTR(text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1),
    '^-20[0-9]{3}$'
  )
ORDER BY owner, name, line;
-- Every row returned is a RAISE_APPLICATION_ERROR call with an error code
-- outside -20000 to -20999 — these will raise ORA-21000 at runtime

-- Example: Three-level call chain with custom errors

-- Level C: Raises the root cause error
CREATE OR REPLACE PROCEDURE app_schema.validate_balance(
  p_account_id IN NUMBER,
  p_amount     IN NUMBER
) IS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance
  FROM data_owner.accounts
  WHERE account_id = p_account_id;

  IF v_balance < p_amount THEN
    RAISE_APPLICATION_ERROR(
      -20102,
      'INSUFFICIENT_BALANCE: account_id=' || p_account_id
      || ' balance=' || v_balance || ' required=' || p_amount
    );
    -- This line creates:
    --   ORA-20102: INSUFFICIENT_BALANCE: account_id=789 balance=50 required=100
    --   ORA-06512: at "APP_SCHEMA.VALIDATE_BALANCE", line 12
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(
      -20101,
      'ACCOUNT_NOT_FOUND: account_id=' || p_account_id,
      TRUE
    );
END validate_balance;
/

-- Level B: Catches, adds context, re-raises with keep_error_stack=TRUE
CREATE OR REPLACE PROCEDURE app_schema.charge_payment(
  p_order_id IN NUMBER
) IS
  v_account_id NUMBER;
  v_amount     NUMBER;
BEGIN
  SELECT account_id, total_amount
  INTO v_account_id, v_amount
  FROM data_owner.orders
  WHERE order_id = p_order_id;

  app_schema.validate_balance(v_account_id, v_amount);
EXCEPTION
  WHEN OTHERS THEN
    -- Add order-level context, preserve the balance error in the stack
    RAISE_APPLICATION_ERROR(
      -20100,
      'PAYMENT_FAILED: order_id=' || p_order_id
      || ' | step_error=' || SQLERRM,
      TRUE  -- preserve the original -20102 error below
    );
    -- This adds:
    --   ORA-20100: PAYMENT_FAILED: order_id=12345 | step_error=...
    --   ORA-06512: at "APP_SCHEMA.CHARGE_PAYMENT", line 18
END charge_payment;
/

-- Level A: The caller — receives the full stack
CREATE OR REPLACE PROCEDURE app_schema.process_order(
  p_order_id IN NUMBER
) IS
BEGIN
  app_schema.charge_payment(p_order_id);
  -- If charge_payment fails, the exception propagates here
  -- Level A has no exception handler, so the error propagates to the application
END process_order;
/

-- The COMPLETE error stack the application sees (with keep_error_stack=TRUE):
--
-- ORA-20100: PAYMENT_FAILED: order_id=12345 | step_error=ORA-20102: INSUFFICIENT_BALANCE...
-- ORA-06512: at "APP_SCHEMA.CHARGE_PAYMENT", line 18
-- ORA-20102: INSUFFICIENT_BALANCE: account_id=789 balance=50 required=100
-- ORA-06512: at "APP_SCHEMA.VALIDATE_BALANCE", line 12
--
-- Reading bottom to top (root cause analysis):
-- 1. Line 12 in VALIDATE_BALANCE: INSUFFICIENT_BALANCE raised (root cause)
-- 2. Line 18 in CHARGE_PAYMENT: PAYMENT_FAILED wrapped it with order context
-- 3. The application sees ORA-20100 at the top — the outermost error

-- WITHOUT keep_error_stack=TRUE (if B used FALSE):
-- ORA-20100: PAYMENT_FAILED: order_id=12345
-- ORA-06512: at "APP_SCHEMA.CHARGE_PAYMENT", line 18
-- (The ORA-20102 INSUFFICIENT_BALANCE and its location are GONE)
-- (The root cause is permanently lost)

-- Propagation Pattern 1: No handler — exception propagates unchanged
CREATE OR REPLACE PROCEDURE app_schema.create_order(
  p_customer_id IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id);
  -- If unique constraint violated: ORA-00001 propagates to the caller
  -- No exception handler — the original error passes through unchanged
  -- The caller sees ORA-00001 + ORA-06512 at this procedure
END create_order;
/

-- Propagation Pattern 2: Handler adds context and re-raises with custom code
CREATE OR REPLACE PROCEDURE app_schema.create_order_v2(
  p_customer_id IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(
      -20010,
      'DUPLICATE_ORDER: customer_id=' || p_customer_id
      || ' — an active order already exists | original=' || SQLERRM,
      TRUE  -- preserve the ORA-00001 in the stack
    );
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(
      -20099,
      'UNEXPECTED_ERROR: module=ORDERS op=CREATE'
      || ' customer_id=' || p_customer_id
      || ' SQLCODE=' || SQLCODE || ' | original=' || SQLERRM,
      TRUE
    );
END create_order_v2;
/

-- Propagation Pattern 3: Nested blocks — inner exception propagates to outer
CREATE OR REPLACE PROCEDURE app_schema.process_order(
  p_order_id IN NUMBER
) IS
BEGIN
  -- Step 1: Validate
  BEGIN
    app_schema.validate_order(p_order_id);
    -- If validate_order raises -20001 (ORDER_NOT_FOUND),
    -- it propagates to the outer handler below
  END;

  -- Step 2: Charge
  BEGIN
    app_schema.charge_payment(p_order_id);
    -- If charge_payment raises -20100 (PAYMENT_FAILED),
    -- it propagates to the outer handler below
  END;

EXCEPTION
  WHEN OTHERS THEN
    -- Outer handler: adds process-level context
    app_schema.logger_pkg.error(
      'process_order',
      'Failed: order_id=' || p_order_id || ' SQLCODE=' || SQLCODE,
      DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    RAISE_APPLICATION_ERROR(
      -20030,
      'ORDER_PROCESSING_FAILED: order_id=' || p_order_id
      || ' | step_error=' || SQLERRM,
      TRUE
    );
END process_order;
/

-- Propagation Pattern 4: FORALL with SAVE EXCEPTIONS
CREATE OR REPLACE PROCEDURE app_schema.process_batch IS
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids;
  v_error_count PLS_INTEGER := 0;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  SELECT order_id BULK COLLECT INTO v_ids
  FROM data_owner.staging_orders
  WHERE processed_flag = 'N';

  BEGIN
    FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
      INSERT INTO data_owner.orders (order_id)
      VALUES (v_ids(i));
  EXCEPTION
    WHEN e_bulk_errors THEN
      v_error_count := SQL%BULK_EXCEPTIONS.COUNT;

      -- Log each individual row failure
      FOR j IN 1 .. v_error_count LOOP
        app_schema.logger_pkg.error(
          'process_batch',
          'Row index=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX
          || ' order_id=' || v_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)
          || ' ORA-' || LPAD(SQL%BULK_EXCEPTIONS(j).ERROR_CODE, 5, '0')
        );
      END LOOP;

      -- Raise summary error
      RAISE_APPLICATION_ERROR(
        -20098,
        'BATCH_PARTIAL_FAILURE: ' || v_error_count || ' of '
        || v_ids.COUNT || ' rows failed — see error log for details'
      );
  END;

  COMMIT;
END process_batch;
/

-- ANTI-PATTERN: WHEN OTHERS that does NOT re-raise — silently swallows errors
CREATE OR REPLACE PROCEDURE app_schema.create_order_silent(
  p_customer_id IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id);
EXCEPTION
  WHEN OTHERS THEN
    -- WRONG: catches the error, does nothing, returns normally
    -- The caller thinks the order was created successfully
    -- But the INSERT failed — data is now inconsistent
    NULL;
END create_order_silent;
/
-- This procedure returns SUCCESS even when the INSERT fails
-- The caller has no way to know the order was not created

-- Layer 1: Error registry table — single source of truth
CREATE TABLE app_schema.error_registry (
  error_code    NUMBER(6)     PRIMARY KEY
    CONSTRAINT chk_error_range CHECK (error_code BETWEEN -20999 AND -20000),
  error_name    VARCHAR2(60)  NOT NULL UNIQUE,
  description   VARCHAR2(500) NOT NULL,
  module        VARCHAR2(30)  NOT NULL,
  severity      VARCHAR2(10)  NOT NULL
    CONSTRAINT chk_severity CHECK (severity IN ('LOW','MEDIUM','HIGH','CRITICAL')),
  retryable     VARCHAR2(1)   NOT NULL
    CONSTRAINT chk_retryable CHECK (retryable IN ('Y','N')),
  recovery_hint VARCHAR2(200) NOT NULL,
  status        VARCHAR2(10)  DEFAULT 'ACTIVE' NOT NULL
    CONSTRAINT chk_status CHECK (status IN ('ACTIVE','DEPRECATED')),
  created_date  DATE          DEFAULT SYSDATE NOT NULL,
  created_by    VARCHAR2(60)  DEFAULT USER NOT NULL
);

-- Seed: Orders module (-20000 to -20099)
INSERT INTO app_schema.error_registry VALUES (
  -20001, 'ORDER_NOT_FOUND',
  'The specified order does not exist in the database',
  'ORDERS', 'MEDIUM', 'N',
  'Verify the order_id and re-submit with a valid ID',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20010, 'DUPLICATE_ORDER',
  'An order with this ID or idempotency key already exists',
  'ORDERS', 'HIGH', 'N',
  'Check order history — do NOT retry',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20020, 'ORDER_INVALID_STATE',
  'The order is in a state that does not allow this operation',
  'ORDERS', 'MEDIUM', 'N',
  'Check order status before attempting this operation',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20099, 'ORDER_UNEXPECTED',
  'An unexpected error occurred in the orders module',
  'ORDERS', 'CRITICAL', 'N',
  'Escalate to operations with full error stack',
  'ACTIVE', SYSDATE, USER
);

-- Seed: Payments module (-20100 to -20199)
INSERT INTO app_schema.error_registry VALUES (
  -20100, 'PAYMENT_FAILED',
  'Payment processing failed — see nested error for details',
  'PAYMENTS', 'HIGH', 'N',
  'Check the nested error code for the specific payment failure',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20101, 'ACCOUNT_NOT_FOUND',
  'The payment account does not exist',
  'PAYMENTS', 'MEDIUM', 'N',
  'Verify account_id and re-submit',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20102, 'INSUFFICIENT_BALANCE',
  'Account balance is insufficient for this transaction',
  'PAYMENTS', 'MEDIUM', 'N',
  'Prompt user for a different payment method',
  'ACTIVE', SYSDATE, USER
);

-- Seed: Infrastructure / retryable (-20900 to -20999)
INSERT INTO app_schema.error_registry VALUES (
  -20950, 'DEADLOCK_DETECTED',
  'A deadlock was detected — the operation can be safely retried',
  'INFRASTRUCTURE', 'LOW', 'Y',
  'Retry with exponential backoff (max 3 retries)',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20951, 'OPERATION_TIMEOUT',
  'The operation timed out — may be retryable',
  'INFRASTRUCTURE', 'MEDIUM', 'Y',
  'Retry once — if it fails again, escalate',
  'ACTIVE', SYSDATE, USER
);
INSERT INTO app_schema.error_registry VALUES (
  -20999, 'UNEXPECTED_ERROR',
  'An unexpected error occurred that does not match any known failure mode',
  'INFRASTRUCTURE', 'CRITICAL', 'N',
  'Escalate to operations with full error stack',
  'ACTIVE', SYSDATE, USER
);

COMMIT;

-- Layer 2: Centralized raise procedure — enforces consistent format
CREATE OR REPLACE PROCEDURE app_schema.raise_app_error(
  p_error_code IN NUMBER,
  p_context    IN VARCHAR2 DEFAULT NULL,
  p_keep_stack IN BOOLEAN  DEFAULT TRUE
) IS
  v_rec     app_schema.error_registry%ROWTYPE;
  v_message VARCHAR2(2048);
BEGIN
  BEGIN
    SELECT * INTO v_rec
    FROM app_schema.error_registry
    WHERE error_code = p_error_code
      AND status = 'ACTIVE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_rec.error_name  := 'UNREGISTERED_ERROR';
      v_rec.description := 'Error code ' || p_error_code
        || ' is not registered in the error registry';
  END;

  v_message := v_rec.error_name || ': ' || v_rec.description;
  IF p_context IS NOT NULL THEN
    v_message := v_message || ' | ' || p_context;
  END IF;

  RAISE_APPLICATION_ERROR(p_error_code, v_message, p_keep_stack);
END raise_app_error;
/

-- Layer 3: SQLCODE-to-custom-code mapper (used in WHEN OTHERS handlers)
CREATE OR REPLACE PROCEDURE app_schema.handle_exception(
  p_module    IN VARCHAR2,
  p_operation IN VARCHAR2,
  p_context   IN VARCHAR2 DEFAULT NULL
) IS
  v_sqlcode NUMBER := SQLCODE;
  v_sqlerrm VARCHAR2(512) := SQLERRM;
  v_custom  NUMBER;
BEGIN
  v_custom := CASE v_sqlcode
    WHEN -1    THEN -20010  -- unique constraint -> duplicate
    WHEN -1403 THEN -20001  -- no data found -> not found
    WHEN -1422 THEN -20020  -- too many rows -> invalid state
    WHEN -60   THEN -20950  -- deadlock -> retryable
    WHEN -30006 THEN -20951 -- resource busy timeout -> retryable
    ELSE -20999             -- everything else -> unexpected
  END;

  -- Log before raising
  app_schema.logger_pkg.error(
    p_module || '.' || p_operation,
    'SQLCODE=' || v_sqlcode || ' | ' || v_sqlerrm
    || CASE WHEN p_context IS NOT NULL THEN ' | ' || p_context END,
    DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
  );

  app_schema.raise_app_error(
    p_error_code => v_custom,
    p_context    => 'module=' || p_module
      || ' operation=' || p_operation
      || ' SQLCODE=' || v_sqlcode
      || CASE WHEN p_context IS NOT NULL THEN ' | ' || p_context END
  );
END handle_exception;
/

-- Usage: one-line exception handler in application procedures
CREATE OR REPLACE PROCEDURE app_schema.create_order(
  p_customer_id IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id);
EXCEPTION
  WHEN OTHERS THEN
    app_schema.handle_exception(
      'ORDERS', 'CREATE_ORDER',
      'customer_id=' || p_customer_id
    );
END create_order;
/

-- Pre-deployment check: find unregistered error codes in the codebase
SELECT
  s.owner,
  s.name,
  s.line,
  REGEXP_SUBSTR(s.text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1) AS error_code
FROM dba_source s
WHERE UPPER(s.text) LIKE '%RAISE_APPLICATION_ERROR%'
  AND s.owner NOT IN ('SYS', 'SYSTEM')
  AND REGEXP_SUBSTR(s.text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1) IS NOT NULL
  AND TO_NUMBER(REGEXP_SUBSTR(s.text, 'RAISE_APPLICATION_ERROR\s*\(\s*(-?\d+)', 1, 1, 'i', 1))
    NOT IN (SELECT error_code FROM app_schema.error_registry WHERE status = 'ACTIVE')
ORDER BY s.owner, s.name, s.line;
-- Every row returned is an unregistered error code — block deployment until registered

package io.thecodeforge.errors;

import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * Maps Oracle custom error codes (-20000 to -20999) to application
 * recovery actions. Mirrors the database app_schema.error_registry table.
 *
 * This class is the application side of the database-application error
 * contract. Changes to the database registry must be reflected here.
 * Deploy atomically.
 */
public final class ErrorCodeHandler {

    public enum Severity { LOW, MEDIUM, HIGH, CRITICAL }
    public enum RecoveryAction { RETRY, DISPLAY_ERROR, REDIRECT, ESCALATE }

    public record ErrorDefinition(
        String name,
        String userMessage,
        Severity severity,
        RecoveryAction action,
        boolean retryable,
        int maxRetries
    ) {}

    private static final Map<Integer, ErrorDefinition> REGISTRY =
        new ConcurrentHashMap<>();

    static {
        // Orders module: -20000 to -20099
        REGISTRY.put(20001, new ErrorDefinition(
            "ORDER_NOT_FOUND",
            "The requested order could not be found. Please verify the order number.",
            Severity.MEDIUM, RecoveryAction.DISPLAY_ERROR, false, 0
        ));
        REGISTRY.put(20010, new ErrorDefinition(
            "DUPLICATE_ORDER",
            "This order has already been submitted. Please check your order history.",
            Severity.HIGH, RecoveryAction.DISPLAY_ERROR, false, 0
        ));
        REGISTRY.put(20020, new ErrorDefinition(
            "ORDER_INVALID_STATE",
            "This order cannot be modified in its current state.",
            Severity.MEDIUM, RecoveryAction.DISPLAY_ERROR, false, 0
        ));

        // Payments module: -20100 to -20199
        REGISTRY.put(20101, new ErrorDefinition(
            "ACCOUNT_NOT_FOUND",
            "The payment account was not found. Please verify your account details.",
            Severity.MEDIUM, RecoveryAction.DISPLAY_ERROR, false, 0
        ));
        REGISTRY.put(20102, new ErrorDefinition(
            "INSUFFICIENT_BALANCE",
            "Insufficient balance. Please use a different payment method.",
            Severity.MEDIUM, RecoveryAction.REDIRECT, false, 0
        ));

        // Infrastructure / retryable: -20900 to -20999
        REGISTRY.put(20950, new ErrorDefinition(
            "DEADLOCK_DETECTED",
            "A temporary conflict occurred. Retrying automatically.",
            Severity.LOW, RecoveryAction.RETRY, true, 3
        ));
        REGISTRY.put(20951, new ErrorDefinition(
            "OPERATION_TIMEOUT",
            "The operation timed out. Retrying automatically.",
            Severity.MEDIUM, RecoveryAction.RETRY, true, 2
        ));
        REGISTRY.put(20999, new ErrorDefinition(
            "UNEXPECTED_ERROR",
            "An unexpected error occurred. Our team has been notified.",
            Severity.CRITICAL, RecoveryAction.ESCALATE, false, 0
        ));
    }

    /**
     * Maps a SQLException to the appropriate ErrorDefinition.
     * Oracle reports error codes as positive integers (20001 for ORA-20001).
     * Returns a default ESCALATE definition for unregistered codes.
     */
    public static ErrorDefinition resolve(SQLException ex) {
        int code = ex.getErrorCode();
        ErrorDefinition def = REGISTRY.get(code);

        if (def == null) {
            // Unregistered code — contract is broken — escalate immediately
            return new ErrorDefinition(
                "UNKNOWN_ERROR",
                "An unexpected error occurred. Our team has been notified.",
                Severity.CRITICAL, RecoveryAction.ESCALATE, false, 0
            );
        }
        return def;
    }

    public static boolean isRetryable(SQLException ex) {
        return resolve(ex).retryable();
    }

    public static int maxRetries(SQLException ex) {
        return resolve(ex).maxRetries();
    }

    public static RecoveryAction recoveryAction(SQLException ex) {
        return resolve(ex).action();
    }

    public static String userMessage(SQLException ex) {
        return resolve(ex).userMessage();
    }
}

-- Example error stack (as seen by the application caller):
--
-- ORA-20100: PAYMENT_FAILED: Payment processing failed — see nested error
--            | order_id=12345 | step_error=ORA-20102: INSUFFICIENT_BALANCE...
-- ORA-06512: at "APP_SCHEMA.CHARGE_PAYMENT", line 18
-- ORA-20102: INSUFFICIENT_BALANCE: Account balance is insufficient
--            | account_id=789 balance=50 required=100
-- ORA-06512: at "APP_SCHEMA.VALIDATE_BALANCE", line 12
--
-- Step-by-step reading (bottom to top for root cause analysis):
--
-- 1. Bottom frame: ORA-06512 at VALIDATE_BALANCE line 12
--    -> This is WHERE the root cause error was raised
--
-- 2. Above it: ORA-20102: INSUFFICIENT_BALANCE
--    -> This is WHAT the root cause error is
--    -> Message contains: account_id=789, balance=50, required=100
--    -> Diagnosis: account 789 has balance 50, but 100 was needed
--
-- 3. Next frame: ORA-06512 at CHARGE_PAYMENT line 18
--    -> This is WHERE the error was caught and re-raised with order context
--
-- 4. Top: ORA-20100: PAYMENT_FAILED
--    -> This is WHAT the caller sees — the order-level error
--    -> Message contains: order_id=12345
--    -> The application maps -20100 to the PAYMENT_FAILED recovery action

-- Diagnostic: Find the source code at an ORA-06512 line number
-- Given: ORA-06512: at "APP_SCHEMA.VALIDATE_BALANCE", line 12
SELECT
  line,
  TRIM(text) AS source_line
FROM dba_source
WHERE owner = 'APP_SCHEMA'
  AND name  = 'VALIDATE_BALANCE'
  AND type  = 'PROCEDURE'
  AND line BETWEEN 8 AND 16  -- show context around line 12
ORDER BY line;
-- Returns the source code around line 12
-- The RAISE_APPLICATION_ERROR call at line 12 is the exact raise point

-- Diagnostic: Find ALL RAISE_APPLICATION_ERROR calls in a package or procedure
SELECT
  line,
  TRIM(text) AS source_line
FROM dba_source
WHERE owner = 'APP_SCHEMA'
  AND name  = 'CHARGE_PAYMENT'
  AND UPPER(text) LIKE '%RAISE_APPLICATION_ERROR%'
ORDER BY line;
-- Each result is a potential raise point
-- Match the ORA-06512 line number to find which call raised the error

-- Diagnostic: Look up the error code in the registry
SELECT
  error_code,
  error_name,
  description,
  module,
  severity,
  retryable,
  recovery_hint
FROM app_schema.error_registry
WHERE error_code = -20102;
-- Returns:
--   -20102, INSUFFICIENT_BALANCE, Account balance is insufficient...,
--   PAYMENTS, MEDIUM, N, Prompt user for a different payment method
-- This tells the on-call engineer exactly what the error means
-- and what recovery action to take

-- Diagnostic: Capture and store the full error stack in PL/SQL
-- Use DBMS_UTILITY functions to capture the complete diagnostic context
DECLARE
  v_error_stack    VARCHAR2(4000);
  v_error_bt       VARCHAR2(4000);
  v_call_stack     VARCHAR2(4000);
BEGIN
  -- ... code that may raise an exception ...
EXCEPTION
  WHEN OTHERS THEN
    v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;     -- ORA-XXXXX + message
    v_error_bt    := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; -- ORA-06512 frames
    v_call_stack  := DBMS_UTILITY.FORMAT_CALL_STACK;      -- Current call chain

    -- Log all three for maximum diagnostic value
    app_schema.logger_pkg.error(
      'my_procedure',
      'Error stack: ' || v_error_stack,
      v_error_bt || CHR(10) || v_call_stack
    );
    RAISE;
END;
/

// io.thecodeforge
-- Wrong: Overloading system exception
DECLARE
   ex_credit_limit EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_credit_limit, -100);
BEGIN
   RAISE ex_credit_limit;
EXCEPTION
   WHEN ex_credit_limit THEN
      DBMS_OUTPUT.PUT_LINE('Overloaded -100? Bad idea');
END;
/

-- Right: Use reserved range for business logic
DECLARE
   ex_credit_limit EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_credit_limit, -20001);
BEGIN
   RAISE ex_credit_limit;
EXCEPTION
   WHEN ex_credit_limit THEN
      DBMS_OUTPUT.PUT_LINE('Business rule violation at -20001');
END;
/

// io.thecodeforge
-- Named: Catchable, maintainable
CREATE OR REPLACE PROCEDURE process_payment(
   p_amount NUMBER
) AS
   ex_insufficient_funds EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_insufficient_funds, -20100);
BEGIN
   IF p_amount > 10000 THEN
      RAISE ex_insufficient_funds;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Payment processed');
EXCEPTION
   WHEN ex_insufficient_funds THEN
      DBMS_OUTPUT.PUT_LINE('Hold for manager review');
END;
/

-- Unnamed: Fatal, propagates raw
CREATE OR REPLACE PROCEDURE verify_checksum(
   p_hash VARCHAR2
) AS
BEGIN
   IF p_hash IS NULL THEN
      RAISE_APPLICATION_ERROR(-20200, 'Checksum cannot be null');
   END IF;
END;
/