RAISE_APPLICATION_ERROR — How Generic Codes Cost $2.4M

-- Syntax
-- RAISE_APPLICATION_ERROR(error_number, message [, keep_error_stack]);
--
-- error_number:     INTEGER, must be -20000 to -20999
-- message:          VARCHAR2, up to 2048 bytes
-- keep_error_stack: BOOLEAN, default FALSE
--                   FALSE = replace stack, TRUE = chain onto stack

-- Example 1: Simple custom error with diagnostic context
CREATE OR REPLACE PROCEDURE app_schema.validate_order(
  p_order_id IN NUMBER
) IS
  v_count NUMBER;
BEGIN
  IF p_order_id IS NULL THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'ORDER_NOT_FOUND: order_id is NULL — caller must provide a valid order_id'
    );
  END IF;

  SELECT COUNT(*)
  INTO v_count
  FROM data_owner.orders
  WHERE order_id = p_order_id;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'ORDER_NOT_FOUND: order_id=' || p_order_id || ' does not exist in the orders table'
    );
  END IF;
END validate_order;
/

-- Example 2: Re-raising with stack preservation (keep_error_stack = TRUE)
CREATE OR REPLACE PROCEDURE app_schema.process_payment(
  p_order_id IN NUMBER
) IS
BEGIN
  app_schema.payment_engine.charge(p_order_id);
EXCEPTION
  WHEN OTHERS THEN
    -- Chain the custom error onto the existing stack
    -- The caller sees BOTH the custom code AND the original error
    RAISE_APPLICATION_ERROR(
      -20010,
      'PAYMENT_FAILED: order_id=' || p_order_id
      || ' | original_error=' || SQLCODE || ': ' || SQLERRM,
      TRUE  -- chain onto existing stack
    );
END process_payment;
/

-- Example 3: What the caller sees with stack=FALSE vs. stack=TRUE
--
-- stack=FALSE (default):
--   ORA-20010: PAYMENT_FAILED: order_id=12345 | original_error=-1: ...
--   ORA-06512: at "APP_SCHEMA.PROCESS_PAYMENT", line 12
--   (The original ORA-00001 and its location are GONE)
--
-- stack=TRUE:
--   ORA-20010: PAYMENT_FAILED: order_id=12345 | original_error=-1: ...
--   ORA-06512: at "APP_SCHEMA.PROCESS_PAYMENT", line 12
--   ORA-00001: unique constraint (DATA_OWNER.ORDERS_UK) violated
--   ORA-06512: at "APP_SCHEMA.PAYMENT_ENGINE", line 89
--   (The original ORA-00001 and its exact location are PRESERVED)

-- Example 4: Error number outside valid range
BEGIN
  RAISE_APPLICATION_ERROR(-30001, 'This will not work');
  -- Oracle raises:
  -- ORA-21000: error number argument to raise_application_error
  --            of -30001 is out of range
  -- Your intended error is never raised
END;
/

-- Error registry table — single source of truth for all custom error codes
CREATE TABLE app_schema.error_registry (
  error_code      NUMBER(6)     PRIMARY KEY
    CONSTRAINT chk_error_code CHECK (error_code BETWEEN -20999 AND -20000),
  error_name      VARCHAR2(60)  NOT NULL UNIQUE,
  description     VARCHAR2(500) NOT NULL,
  severity        VARCHAR2(10)  NOT NULL
    CONSTRAINT chk_severity CHECK (severity IN ('LOW','MEDIUM','HIGH','CRITICAL')),
  recovery_action VARCHAR2(200) NOT NULL,
  retryable       VARCHAR2(1)   DEFAULT 'N' NOT NULL
    CONSTRAINT chk_retryable CHECK (retryable IN ('Y','N')),
  module          VARCHAR2(30)  NOT NULL,
  created_date    DATE          DEFAULT SYSDATE NOT NULL
);

-- Order module errors: -20000 to -20099
INSERT INTO app_schema.error_registry VALUES (
  -20001, 'ORDER_NOT_FOUND',
  'The specified order does not exist in the database',
  'MEDIUM', 'Verify the order_id and re-submit with a valid ID', 'N',
  'ORDERS', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20010, 'DUPLICATE_ORDER',
  'An order with this ID or idempotency key already exists — duplicate submission detected',
  'HIGH', 'Check if the order was already processed; do NOT retry', 'N',
  'ORDERS', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20020, 'ORDER_INVALID_STATE',
  'The order is in a state that does not allow this operation',
  'MEDIUM', 'Check order status before attempting this operation', 'N',
  'ORDERS', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20030, 'ORDER_BUSINESS_RULE',
  'A business rule prevents this order operation',
  'MEDIUM', 'Review the business rule details in the error message', 'N',
  'ORDERS', SYSDATE
);

-- Payment module errors: -20100 to -20199
INSERT INTO app_schema.error_registry VALUES (
  -20101, 'PAYMENT_INSUFFICIENT_FUNDS',
  'The payment method has insufficient funds for this transaction',
  'MEDIUM', 'Prompt user for a different payment method', 'N',
  'PAYMENTS', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20102, 'PAYMENT_CARD_EXPIRED',
  'The payment card has expired',
  'MEDIUM', 'Prompt user for updated card details', 'N',
  'PAYMENTS', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20110, 'PAYMENT_DUPLICATE',
  'This payment has already been processed — duplicate charge attempt',
  'HIGH', 'Do NOT retry; verify original payment was successful', 'N',
  'PAYMENTS', SYSDATE
);

-- Infrastructure / retryable errors: -20900 to -20999
INSERT INTO app_schema.error_registry VALUES (
  -20950, 'DEADLOCK_DETECTED',
  'A deadlock was detected — the operation can be safely retried',
  'LOW', 'Retry with exponential backoff', 'Y',
  'INFRASTRUCTURE', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20951, 'LOCK_TIMEOUT',
  'A lock wait timeout occurred — the operation can be safely retried',
  'MEDIUM', 'Retry with exponential backoff', 'Y',
  'INFRASTRUCTURE', SYSDATE
);
INSERT INTO app_schema.error_registry VALUES (
  -20999, 'UNEXPECTED_ERROR',
  'An unexpected error occurred that does not match any known failure mode',
  'CRITICAL', 'Escalate to operations team with full error stack', 'N',
  'INFRASTRUCTURE', SYSDATE
);

COMMIT;

-- Centralized raise procedure — enforces consistent message 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;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Unregistered code — raise with a warning prefix
      v_message := 'UNREGISTERED_ERROR: code=' || p_error_code;
      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);
      RETURN;
  END;

  -- Build a structured, parseable message
  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;
/

-- Usage in application code
CREATE OR REPLACE PROCEDURE app_schema.create_order(
  p_customer_id IN NUMBER,
  p_product_id  IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id, product_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id, p_product_id);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    app_schema.raise_app_error(
      -20010,
      'customer_id=' || p_customer_id || ' product_id=' || p_product_id
      || ' | original=' || SQLERRM
    );
  WHEN OTHERS THEN
    app_schema.raise_app_error(
      -20999,
      'customer_id=' || p_customer_id || ' SQLCODE=' || SQLCODE
      || ' | original=' || SQLERRM
    );
END create_order;
/

-- Scenario 1: keep_error_stack=FALSE (default) — LOSES the original error
CREATE OR REPLACE PROCEDURE app_schema.create_order_v1(
  p_customer_id IN NUMBER,
  p_product_id  IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id, product_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id, p_product_id);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- keep_error_stack=FALSE (implicit default): original ORA-00001 is LOST
    RAISE_APPLICATION_ERROR(
      -20010,
      'DUPLICATE_ORDER: customer_id=' || p_customer_id
      || ' product_id=' || p_product_id
    );
    -- Caller sees:
    --   ORA-20010: DUPLICATE_ORDER: customer_id=100 product_id=200
    --   ORA-06512: at "APP_SCHEMA.CREATE_ORDER_V1", line 12
    -- Original ORA-00001 is NOT in the stack
    -- Which constraint? Which table? Unknown from the stack alone.
END create_order_v1;
/

-- Scenario 2: keep_error_stack=TRUE — PRESERVES the original error
CREATE OR REPLACE PROCEDURE app_schema.create_order_v2(
  p_customer_id IN NUMBER,
  p_product_id  IN NUMBER
) IS
BEGIN
  INSERT INTO data_owner.orders (order_id, customer_id, product_id)
  VALUES (data_owner.orders_seq.NEXTVAL, p_customer_id, p_product_id);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- keep_error_stack=TRUE: original ORA-00001 is PRESERVED
    RAISE_APPLICATION_ERROR(
      -20010,
      'DUPLICATE_ORDER: customer_id=' || p_customer_id
      || ' product_id=' || p_product_id,
      TRUE
    );
    -- Caller sees:
    --   ORA-20010: DUPLICATE_ORDER: customer_id=100 product_id=200
    --   ORA-06512: at "APP_SCHEMA.CREATE_ORDER_V2", line 12
    --   ORA-00001: unique constraint (DATA_OWNER.ORDERS_UK) violated
    --   ORA-06512: at "APP_SCHEMA.CREATE_ORDER_V2", line 5
    -- Original ORA-00001 IS in the stack, including which constraint
END create_order_v2;
/

-- Scenario 3: Nested handlers — TRUE at each level preserves full chain
CREATE OR REPLACE PROCEDURE app_schema.process_order(
  p_order_id IN NUMBER
) IS
BEGIN
  app_schema.validate_order(p_order_id);
  app_schema.process_payment(p_order_id);
  app_schema.update_inventory(p_order_id);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(
      -20030,
      'ORDER_PROCESSING_FAILED: order_id=' || p_order_id
      || ' | step_error=' || SQLERRM,
      TRUE  -- preserve the payment/inventory error context
    );
END process_order;
/

-- When process_payment raises -20010 (DUPLICATE_ORDER) with stack=TRUE,
-- and process_order re-raises with -20030 with stack=TRUE,
-- the caller sees the COMPLETE chain:
--
--   ORA-20030: ORDER_PROCESSING_FAILED: order_id=12345 | step_error=...
--   ORA-06512: at "APP_SCHEMA.PROCESS_ORDER", line 14
--   ORA-20010: DUPLICATE_ORDER: customer_id=100 product_id=200
--   ORA-06512: at "APP_SCHEMA.PROCESS_PAYMENT", line 12
--   ORA-00001: unique constraint (DATA_OWNER.ORDERS_UK) violated
--   ORA-06512: at "APP_SCHEMA.PROCESS_PAYMENT", line 5
--
-- Full context: what the business operation was, what the intermediate
-- step was, and what the database-level failure was.

-- ANTI-PATTERN: Generic WHEN OTHERS — destroys all diagnostic value
CREATE OR REPLACE PROCEDURE app_schema.create_order_bad(
  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: Every exception becomes the same code and message
    RAISE_APPLICATION_ERROR(-20001, 'Order creation failed');
    -- Caller sees: ORA-20001: Order creation failed
    -- Was it a unique constraint? Privilege error? Deadlock? Tablespace full?
    -- Impossible to determine. Original error is destroyed.
END create_order_bad;
/

-- CORRECT: Specific handlers first, WHEN OTHERS with full context last
CREATE OR REPLACE PROCEDURE app_schema.create_order_good(
  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
    -- Specific: duplicate order
    RAISE_APPLICATION_ERROR(
      -20010,
      'DUPLICATE_ORDER: customer_id=' || p_customer_id
      || ' | original=' || SQLERRM,
      TRUE
    );
  WHEN OTHERS THEN
    -- Fallback: unexpected error — preserve EVERYTHING
    IF SQLCODE = -60 THEN
      -- Deadlock — retryable
      RAISE_APPLICATION_ERROR(
        -20950,
        'DEADLOCK_DETECTED: customer_id=' || p_customer_id
        || ' | original=' || SQLERRM,
        TRUE
      );
    ELSE
      -- Genuinely unexpected — log and raise with full context
      app_schema.logger_pkg.error(
        'create_order',
        'Unexpected error: customer_id=' || p_customer_id
        || ' SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM,
        DBMS_UTILITY.FORMAT_ERROR_STACK
        || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      );
      RAISE_APPLICATION_ERROR(
        -20999,
        'UNEXPECTED_ERROR: module=ORDERS op=CREATE_ORDER'
        || ' customer_id=' || p_customer_id
        || ' SQLCODE=' || SQLCODE
        || ' | original=' || SQLERRM,
        TRUE
      );
    END IF;
END create_order_good;
/

-- BEST: Centralized exception handler — eliminates per-procedure boilerplate
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_backtrace   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  v_custom_code NUMBER;
  v_message     VARCHAR2(2048);
BEGIN
  -- Map the original SQLCODE to a custom error code
  v_custom_code := 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 -> ambiguous state
    WHEN -60   THEN -20950   -- deadlock -> retryable
    WHEN -30006 THEN -20951  -- lock timeout -> retryable
    ELSE -20999              -- everything else -> unexpected
  END;

  -- Build structured, parseable message
  v_message := p_module || '.' || p_operation
    || ': SQLCODE=' || v_sqlcode
    || ' | original=' || v_sqlerrm;
  IF p_context IS NOT NULL THEN
    v_message := v_message || ' | context=' || p_context;
  END IF;

  -- Log before raising (autonomous transaction ensures log survives rollback)
  app_schema.logger_pkg.error(
    p_module || '.' || p_operation,
    v_message,
    DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || v_backtrace
  );

  RAISE_APPLICATION_ERROR(v_custom_code, v_message, TRUE);
END handle_exception;
/

-- Usage: one line replaces the entire exception handler
CREATE OR REPLACE PROCEDURE app_schema.create_order_best(
  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_best;
/

package io.thecodeforge.exceptions;

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 error_registry table.
 *
 * This class is the application side of the database-application
 * error contract. Changes to the database error_registry must be
 * reflected here — deploy atomically.
 */
public final class ErrorCodeMapper {

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

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

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

    static {
        // Order 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, Action.DISPLAY_ERROR, false, 0
        ));
        REGISTRY.put(20010, new ErrorDefinition(
            "DUPLICATE_ORDER",
            "This order has already been submitted. Please check your order history.",
            Severity.HIGH, Action.DISPLAY_ERROR, false, 0
        ));
        REGISTRY.put(20020, new ErrorDefinition(
            "ORDER_INVALID_STATE",
            "This order cannot be modified in its current state.",
            Severity.MEDIUM, Action.DISPLAY_ERROR, false, 0
        ));

        // Payment module: -20100 to -20199
        REGISTRY.put(20101, new ErrorDefinition(
            "PAYMENT_INSUFFICIENT_FUNDS",
            "Your payment method has insufficient funds. Please use a different card.",
            Severity.MEDIUM, Action.REDIRECT, false, 0
        ));
        REGISTRY.put(20102, new ErrorDefinition(
            "PAYMENT_CARD_EXPIRED",
            "Your card has expired. Please update your payment details.",
            Severity.MEDIUM, Action.REDIRECT, false, 0
        ));
        REGISTRY.put(20110, new ErrorDefinition(
            "PAYMENT_DUPLICATE",
            "This payment has already been processed.",
            Severity.HIGH, Action.DISPLAY_ERROR, false, 0
        ));

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

    /**
     * Maps a SQLException to the appropriate error definition.
     * Oracle error codes are negative; the registry uses positive keys.
     * Returns a default ESCALATE definition for unregistered codes.
     */
    public static ErrorDefinition map(SQLException ex) {
        // Oracle returns negative codes; registry uses positive for readability
        int code = Math.abs(ex.getErrorCode());
        ErrorDefinition def = REGISTRY.get(code);

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

    /** Convenience: is this error safe to retry? */
    public static boolean isRetryable(SQLException ex) {
        return map(ex).retryable();
    }

    /** Convenience: get the user-facing message for this error. */
    public static String getUserMessage(SQLException ex) {
        return map(ex).userMessage();
    }
}

-- Mechanism 1: RAISE — re-raise the current exception unchanged
CREATE OR REPLACE PROCEDURE app_schema.find_order_raise(
  p_order_id IN NUMBER,
  p_result   OUT data_owner.orders%ROWTYPE
) IS
BEGIN
  SELECT * INTO p_result
  FROM data_owner.orders
  WHERE order_id = p_order_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Log the error, then re-raise unchanged
    app_schema.logger_pkg.warn(
      'find_order',
      'Order not found: order_id=' || p_order_id
    );
    RAISE;  -- Caller sees ORA-01403: no data found (unchanged)
END find_order_raise;
/

-- Mechanism 2: RAISE_APPLICATION_ERROR — custom code and message
CREATE OR REPLACE PROCEDURE app_schema.find_order_custom(
  p_order_id IN NUMBER,
  p_result   OUT data_owner.orders%ROWTYPE
) IS
BEGIN
  SELECT * INTO p_result
  FROM data_owner.orders
  WHERE order_id = p_order_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Custom error code and message — caller sees ORA-20001
    RAISE_APPLICATION_ERROR(
      -20001,
      'ORDER_NOT_FOUND: order_id=' || p_order_id,
      TRUE
    );
END find_order_custom;
/

-- Mechanism 3: Implicit propagation — no handler at all
CREATE OR REPLACE PROCEDURE app_schema.find_order_implicit(
  p_order_id IN NUMBER,
  p_result   OUT data_owner.orders%ROWTYPE
) IS
BEGIN
  SELECT * INTO p_result
  FROM data_owner.orders
  WHERE order_id = p_order_id;
  -- No exception handler
  -- If NO_DATA_FOUND: ORA-01403 propagates as-is to the caller
  -- If any other error: that error propagates as-is to the caller
END find_order_implicit;
/

-- Mechanism 4: Combined — log, add context, re-raise with custom code
CREATE OR REPLACE PROCEDURE app_schema.process_order(
  p_order_id IN NUMBER
) IS
BEGIN
  app_schema.validate_order(p_order_id);    -- may raise -20001
  app_schema.process_payment(p_order_id);   -- may raise -20010, -20101
  app_schema.update_inventory(p_order_id);  -- may raise -20201
EXCEPTION
  WHEN OTHERS THEN
    -- Log with full context
    app_schema.logger_pkg.error(
      'process_order',
      'Failed: order_id=' || p_order_id
      || ' SQLCODE=' || SQLCODE
      || ' | ' || SQLERRM,
      DBMS_UTILITY.FORMAT_ERROR_STACK
      || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    -- Re-raise with higher-level context, preserving the full chain
    RAISE_APPLICATION_ERROR(
      -20030,
      'ORDER_PROCESSING_FAILED: order_id=' || p_order_id
      || ' | step_error=' || SQLERRM,
      TRUE
    );
END process_order;
/

-- Decision tree summary:
-- Caller is an application needing a specific error code -> RAISE_APPLICATION_ERROR
-- Need to log/clean up but not change the error            -> RAISE
-- Internal procedure, caller handles raw Oracle errors     -> No handler (implicit)
-- Re-raise with context at each call layer                 -> RAISE_APPLICATION_ERROR(..., TRUE)

// io.thecodeforge
-- Bad: mixing exception types in one handler
DECLARE
  e_insufficient_balance EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_insufficient_balance, -20001);
BEGIN
  -- This RAISE fires a user-defined exception, not RAISE_APPLICATION_ERROR
  RAISE e_insufficient_balance;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Silent failure for a different error type
    NULL;
  WHEN e_insufficient_balance THEN
    -- No error code mapping back to app
    DBMS_OUTPUT.PUT_LINE('Insufficient balance');
END;

// io.thecodeforge
CREATE OR REPLACE PROCEDURE fail_on_purchase(
  p_product_id IN NUMBER
) AS
  v_sqlerrm VARCHAR2(4000);
BEGIN
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = p_product_id;
  -- Assume deadlock here
EXCEPTION
  WHEN OTHERS THEN
    -- Capture current context before it's lost
    v_sqlerrm := SQLERRM;
    -- Log to audit table with original error
    INSERT INTO error_log (timestamp, sqlcode, sqlerrm, context)
    VALUES (SYSTIMESTAMP, SQLCODE, v_sqlerrm, 'purchase_flow');
    -- Now raise your custom error, preserving stack
    RAISE_APPLICATION_ERROR(-20102, 
      'Purchase failed: ' || v_sqlerrm, TRUE);
END;