PL/SQL Exception Handling — Silent 14k from WHEN OTHERS

-- ============================================================
-- PATTERN 1: Log and re-raise (procedure level)
-- Standard handler for most procedures
-- ============================================================

CREATE OR REPLACE PROCEDURE charge_account(
  p_account_id IN NUMBER,
  p_amount     IN NUMBER
) IS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance
  FROM accounts
  WHERE account_id = p_account_id;

  IF v_balance < p_amount THEN
    RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance for account ' || p_account_id);
  END IF;

  UPDATE accounts
  SET balance = balance - p_amount
  WHERE account_id = p_account_id;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Specific handler: account does not exist
    error_pkg.log_and_raise_custom(
      p_module  => 'charge_account',
      p_message => 'Account not found: ' || p_account_id,
      p_errno   => -20002
    );

  WHEN OTHERS THEN
    -- Generic handler: capture full stack, log, re-raise
    error_pkg.log_and_reraise(p_module => 'charge_account');
END charge_account;
/

-- ============================================================
-- PATTERN 2: Log and continue (batch transaction level)
-- Process each record independently — never halt the batch
-- ============================================================

CREATE OR REPLACE PROCEDURE process_payment_batch(
  p_batch_date      IN DATE,
  p_processed_count OUT NUMBER,
  p_error_count     OUT NUMBER
) IS
BEGIN
  p_processed_count := 0;
  p_error_count     := 0;

  FOR rec IN (
    SELECT transaction_id, account_id, amount
    FROM pending_transactions
    WHERE status = 'PENDING'
      AND batch_date = p_batch_date
    ORDER BY transaction_id
  ) LOOP
    BEGIN
      -- Process individual transaction
      charge_account(rec.account_id, rec.amount);

      UPDATE pending_transactions
      SET status = 'PROCESSED', processed_at = SYSTIMESTAMP
      WHERE transaction_id = rec.transaction_id;

      p_processed_count := p_processed_count + 1;

      IF MOD(p_processed_count, 1000) = 0 THEN
        COMMIT;
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Capture error context FIRST — before any SQL
        DECLARE
          v_stack VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
          v_bt    VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
          v_code  NUMBER         := SQLCODE;
        BEGIN
          -- Log the failure using autonomous transaction
          log_batch_error(
            p_batch_date  => p_batch_date,
            p_txn_id      => rec.transaction_id,
            p_error_code  => v_code,
            p_error_stack => v_stack || CHR(10) || v_bt
          );

          -- Mark the transaction as failed
          UPDATE pending_transactions
          SET status = 'FAILED',
              error_message = SUBSTR(v_stack, 1, 2000)
          WHERE transaction_id = rec.transaction_id;

          p_error_count := p_error_count + 1;
          -- Continue to next transaction — do NOT halt the batch
        END;
    END;
  END LOOP;

  COMMIT;

  -- Post-batch reconciliation: alert if error rate exceeds threshold
  IF p_error_count > 0 AND (p_processed_count + p_error_count) > 0 THEN
    DECLARE
      v_error_pct NUMBER := ROUND(
        100 * p_error_count / (p_processed_count + p_error_count), 1
      );
    BEGIN
      IF v_error_pct > 5 THEN
        RAISE_APPLICATION_ERROR(
          -20020,
          'Batch completed with ' || v_error_pct || '% error rate ('
          || p_error_count || ' of ' || (p_processed_count + p_error_count)
          || '). Check batch_error_log for details.'
        );
      END IF;
    END;
  END IF;

END process_payment_batch;
/

-- ============================================================
-- PATTERN 3: Log and return safe response (API boundary)
-- Never expose raw Oracle errors to clients
-- ============================================================

CREATE OR REPLACE FUNCTION api_get_balance(
  p_account_id IN NUMBER
) RETURN VARCHAR2 IS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance
  FROM accounts
  WHERE account_id = p_account_id;

  RETURN '{"status":"success","balance":' || v_balance || '}';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 404 equivalent — account does not exist
    RETURN '{"status":"error","code":"NOT_FOUND","message":"Account not found"}';

  WHEN OTHERS THEN
    -- Log full stack server-side — do not expose to client
    error_pkg.log_and_reraise(p_module => 'api_get_balance');
    -- If the above re-raises, execution stops here
    -- The calling web server catches the exception and returns HTTP 500
    RETURN '{"status":"error","code":"INTERNAL","message":"An unexpected error occurred"}';
END api_get_balance;
/

-- ============================================================
-- WRONG: SQL before error capture — buffer is overwritten
-- ============================================================

CREATE OR REPLACE PROCEDURE wrong_capture_order IS
  v_dummy VARCHAR2(1);
BEGIN
  SELECT dummy INTO v_dummy FROM dual WHERE 1 = 0;  -- Raises ORA-01403

EXCEPTION
  WHEN OTHERS THEN
    -- This INSERT overwrites the error buffer
    INSERT INTO app_error_log (module, error_stack, created_at)
    VALUES ('wrong_capture', DBMS_UTILITY.FORMAT_ERROR_STACK, SYSTIMESTAMP);
    -- ^^^ FORMAT_ERROR_STACK is called AFTER the INSERT
    -- The INSERT succeeded — the buffer now contains the INSERT result
    -- The log entry shows empty or wrong error information
    COMMIT;
    RAISE;
END wrong_capture_order;
/

-- ============================================================
-- CORRECT: Capture into local variables FIRST, then SQL
-- ============================================================

CREATE OR REPLACE PROCEDURE correct_capture_order IS
  v_dummy VARCHAR2(1);
BEGIN
  SELECT dummy INTO v_dummy FROM dual WHERE 1 = 0;

EXCEPTION
  WHEN OTHERS THEN
    -- Step 1: Capture ALL error context as first statements
    -- These are PL/SQL assignments, not SQL — they do not overwrite the buffer
    DECLARE
      v_error_stack  VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
      v_backtrace    VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      v_error_code   NUMBER         := SQLCODE;
      v_call_stack   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;
    BEGIN
      -- Step 2: Now safe to execute SQL — error context is in local variables
      INSERT INTO app_error_log (
        module, error_code, error_stack, backtrace, call_stack, created_at
      ) VALUES (
        'correct_capture', v_error_code, v_error_stack,
        v_backtrace, v_call_stack, SYSTIMESTAMP
      );
      COMMIT;

      -- Step 3: Re-raise the original exception
      RAISE;
    END;
END correct_capture_order;
/

-- ============================================================
-- CORRECT: Reusable autonomous transaction logging procedure
-- Captures error context at the top, logs with independent commit
-- ============================================================

CREATE OR REPLACE PROCEDURE log_error(
  p_module       IN VARCHAR2,
  p_context_info IN VARCHAR2 DEFAULT NULL
) IS
  -- Capture error context IMMEDIATELY — before any SQL in this procedure
  v_error_stack  VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
  v_backtrace    VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  v_error_code   NUMBER         := SQLCODE;
  v_call_stack   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;

  -- Autonomous transaction: commits independently of caller
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_error_log (
    module, error_code, error_stack, backtrace, call_stack,
    context_info, created_at
  ) VALUES (
    p_module, v_error_code, v_error_stack, v_backtrace, v_call_stack,
    p_context_info, SYSTIMESTAMP
  );
  COMMIT;  -- Commits only this autonomous transaction
  -- Caller's transaction is unaffected — rollback does not erase this log entry
END log_error;
/

-- ============================================================
-- Usage: clean exception handler with reusable logging
-- ============================================================

CREATE OR REPLACE PROCEDURE process_refund(
  p_order_id IN NUMBER,
  p_amount   IN NUMBER
) IS
  v_status VARCHAR2(20);
BEGIN
  SELECT status INTO v_status
  FROM orders
  WHERE order_id = p_order_id;

  IF v_status != 'COMPLETED' THEN
    RAISE_APPLICATION_ERROR(-20010,
      'Cannot refund order ' || p_order_id || ' — status is ' || v_status);
  END IF;

  UPDATE orders SET status = 'REFUNDED' WHERE order_id = p_order_id;
  UPDATE accounts SET balance = balance + p_amount WHERE order_id = p_order_id;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    log_error('process_refund', 'order_id=' || p_order_id);
    RAISE_APPLICATION_ERROR(-20002,
      'Order not found: ' || p_order_id, TRUE);

  WHEN OTHERS THEN
    log_error('process_refund', 'order_id=' || p_order_id || ', amount=' || p_amount);
    RAISE;  -- Re-raise original exception
END process_refund;
/

-- ============================================================
-- WRONG: keep_errors defaults to FALSE — original error is lost
-- ============================================================

DECLARE
  v_result NUMBER;
BEGIN
  v_result := 1 / 0;  -- Raises ORA-01476: divisor is equal to zero
EXCEPTION
  WHEN OTHERS THEN
    -- FALSE is the default — discards ORA-01476 from the stack
    RAISE_APPLICATION_ERROR(-20000, 'Calculation failed');
    -- Error stack now shows ONLY: ORA-20000: Calculation failed
    -- ORA-01476 is gone — post-mortem cannot determine root cause
END;
/

-- ============================================================
-- CORRECT: keep_errors TRUE — original error chain preserved
-- ============================================================

DECLARE
  v_result NUMBER;
BEGIN
  v_result := 1 / 0;
EXCEPTION
  WHEN OTHERS THEN
    DECLARE
      v_original VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
    BEGIN
      -- TRUE preserves the original error in the stack
      RAISE_APPLICATION_ERROR(
        -20010,
        'Tax calculation failed for order processing | ' || v_original,
        TRUE  -- keep_errors: preserve original error chain
      );
    END;
    -- Error stack now shows:
    -- ORA-20010: Tax calculation failed for order processing | ORA-01476: divisor is equal to zero
    -- ORA-06512: at line 12
    -- ORA-01476: divisor is equal to zero   <- Original error preserved
END;
/

-- ============================================================
-- CORRECT: New error (not a re-raise) — FALSE is appropriate
-- ============================================================

CREATE OR REPLACE PROCEDURE validate_order_amount(
  p_amount IN NUMBER
) IS
BEGIN
  IF p_amount IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Order amount cannot be null');
    -- FALSE (default) is correct here — no prior error to preserve
  END IF;

  IF p_amount <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Order amount must be positive: ' || p_amount);
  END IF;

  IF p_amount > 1000000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Order amount exceeds maximum: ' || p_amount);
  END IF;
END validate_order_amount;
/

-- ============================================================
-- Centralized error code constants
-- One package, all codes documented, enforced by code review
-- ============================================================

CREATE OR REPLACE PACKAGE app_errors AS
  -- Validation errors: -20001 to -20009
  c_validation      CONSTANT NUMBER := -20001;
  -- Not found errors: -20010 to -20019
  c_not_found       CONSTANT NUMBER := -20010;
  -- Authorization errors: -20020 to -20029
  c_auth_denied     CONSTANT NUMBER := -20020;
  -- Business rule violations: -20030 to -20039
  c_business_rule   CONSTANT NUMBER := -20030;
  -- External service errors: -20040 to -20049
  c_external_svc    CONSTANT NUMBER := -20040;
  -- System/internal errors: -20100 to -20199
  c_system_error    CONSTANT NUMBER := -20100;

  -- Raise validation with field context
  PROCEDURE raise_validation(p_field IN VARCHAR2, p_message IN VARCHAR2);
  -- Raise not-found with entity context
  PROCEDURE raise_not_found(p_entity IN VARCHAR2, p_id IN VARCHAR2);
  -- Re-raise with business context — preserves original stack
  PROCEDURE reraise_with_context(p_module IN VARCHAR2, p_context IN VARCHAR2);
  -- Translate error code to user-safe message
  FUNCTION user_message(p_error_code IN NUMBER) RETURN VARCHAR2;
END app_errors;
/

CREATE OR REPLACE PACKAGE BODY app_errors AS

  PROCEDURE raise_validation(p_field IN VARCHAR2, p_message IN VARCHAR2) IS
  BEGIN
    RAISE_APPLICATION_ERROR(c_validation,
      'Validation [' || p_field || ']: ' || p_message);
  END;

  PROCEDURE raise_not_found(p_entity IN VARCHAR2, p_id IN VARCHAR2) IS
  BEGIN
    RAISE_APPLICATION_ERROR(c_not_found,
      p_entity || ' not found: ' || p_id);
  END;

  PROCEDURE reraise_with_context(p_module IN VARCHAR2, p_context IN VARCHAR2) IS
    v_original VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
  BEGIN
    log_error(p_module, p_context);  -- Autonomous transaction logging
    RAISE_APPLICATION_ERROR(
      c_system_error,
      p_context || ' | ' || SUBSTR(v_original, 1, 800),
      TRUE  -- Preserve original error chain
    );
  END;

  FUNCTION user_message(p_error_code IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    RETURN CASE
      WHEN p_error_code BETWEEN c_validation  AND c_validation + 8
        THEN 'Invalid input provided'
      WHEN p_error_code BETWEEN c_not_found   AND c_not_found + 9
        THEN 'Requested item not found'
      WHEN p_error_code BETWEEN c_auth_denied AND c_auth_denied + 9
        THEN 'Access denied'
      WHEN p_error_code BETWEEN c_business_rule AND c_business_rule + 9
        THEN 'Operation not allowed'
      WHEN p_error_code BETWEEN c_external_svc AND c_external_svc + 9
        THEN 'External service unavailable — please retry'
      ELSE 'An unexpected error occurred'
    END;
  END;

END app_errors;
/

-- ============================================================
-- Inner function: NO exception handler
-- Let the caller decide what missing data means
-- ============================================================

CREATE OR REPLACE FUNCTION get_balance(
  p_account_id IN NUMBER
) RETURN NUMBER IS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance
  FROM accounts
  WHERE account_id = p_account_id;
  RETURN v_balance;
  -- NO exception handler — NO_DATA_FOUND propagates to caller
  -- The caller has business context to decide the response
END get_balance;
/

-- ============================================================
-- Procedure level: catch with context, re-raise
-- Has business context to make recovery decisions
-- ============================================================

CREATE OR REPLACE PROCEDURE process_payment(
  p_account_id IN NUMBER,
  p_amount     IN NUMBER
) IS
  v_balance NUMBER;
BEGIN
  v_balance := get_balance(p_account_id);
  -- If get_balance raises NO_DATA_FOUND, we catch it below
  -- with full business context

  IF v_balance < p_amount THEN
    app_errors.raise_validation('amount',
      'Insufficient balance (' || v_balance || ') for payment of ' || p_amount);
  END IF;

  UPDATE accounts SET balance = balance - p_amount
  WHERE account_id = p_account_id;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- We know the context: account_id was not found in accounts table
    log_error('process_payment', 'account_id=' || p_account_id);
    app_errors.raise_not_found('Account', TO_CHAR(p_account_id));

  WHEN OTHERS THEN
    log_error('process_payment',
      'account_id=' || p_account_id || ', amount=' || p_amount);
    RAISE;
END process_payment;
/

-- ============================================================
-- Batch level: catch, log, mark failed, continue
-- ============================================================

CREATE OR REPLACE PROCEDURE run_daily_payments IS
  v_ok  NUMBER := 0;
  v_err NUMBER := 0;
BEGIN
  FOR rec IN (SELECT * FROM payment_queue WHERE status = 'PENDING') LOOP
    BEGIN
      process_payment(rec.account_id, rec.amount);
      UPDATE payment_queue SET status = 'DONE' WHERE queue_id = rec.queue_id;
      v_ok := v_ok + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DECLARE
          v_stack VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
        BEGIN
          log_error('run_daily_payments',
            'queue_id=' || rec.queue_id || ', account=' || rec.account_id);
          UPDATE payment_queue
          SET status = 'FAILED', error_msg = SUBSTR(v_stack, 1, 2000)
          WHERE queue_id = rec.queue_id;
          v_err := v_err + 1;
        END;
    END;
  END LOOP;
  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Completed: ' || v_ok || ' ok, ' || v_err || ' failed');
END run_daily_payments;
/

-- ============================================================
-- API boundary: catch, log server-side, return safe response
-- ============================================================

CREATE OR REPLACE FUNCTION api_process_payment(
  p_account_id IN NUMBER,
  p_amount     IN NUMBER
) RETURN VARCHAR2 IS
BEGIN
  process_payment(p_account_id, p_amount);
  RETURN '{"status":"success"}';

EXCEPTION
  WHEN OTHERS THEN
    DECLARE
      v_code NUMBER := SQLCODE;
    BEGIN
      log_error('api_process_payment',
        'account_id=' || p_account_id || ', amount=' || p_amount);
      -- Map Oracle error code to user-safe message
      RETURN '{"status":"error","message":"'
        || app_errors.user_message(v_code) || '"}';
      -- Never expose ORA-XXXXX codes, table names, or SQL text to clients
    END;
END api_process_payment;
/

-- ============================================================
-- Test 1: NO_DATA_FOUND handler raises correct custom error
-- ============================================================

CREATE OR REPLACE PROCEDURE test_not_found_raises IS
BEGIN
  BEGIN
    process_payment(-99999, 100);  -- Non-existent account
    -- If we reach this line, the handler did not raise
    RAISE_APPLICATION_ERROR(-20901, 'FAIL: expected exception but got success');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = app_errors.c_not_found THEN
        DBMS_OUTPUT.PUT_LINE('PASS: NO_DATA_FOUND raises c_not_found (-20010)');
      ELSIF SQLCODE = -20901 THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: handler did not raise — returned silently');
        RAISE;
      ELSE
        DBMS_OUTPUT.PUT_LINE('FAIL: expected -20010, got ' || SQLCODE);
        RAISE;
      END IF;
  END;
END test_not_found_raises;
/

-- ============================================================
-- Test 2: WHEN OTHERS logs correctly and re-raises
-- ============================================================

CREATE OR REPLACE PROCEDURE test_when_others_logs IS
  v_log_before NUMBER;
  v_log_after  NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_log_before
  FROM app_error_log WHERE module = 'process_refund';

  BEGIN
    -- Force an error: refund for non-existent order
    process_refund(-99999, 50);
    RAISE_APPLICATION_ERROR(-20902, 'FAIL: expected exception');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE NOT IN (-20002, -20902) THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: unexpected error code ' || SQLCODE);
        RAISE;
      END IF;
      IF SQLCODE = -20902 THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: handler did not raise');
        RAISE;
      END IF;
  END;

  SELECT COUNT(*) INTO v_log_after
  FROM app_error_log WHERE module = 'process_refund';

  IF v_log_after > v_log_before THEN
    -- Verify the log entry has a non-empty stack
    DECLARE
      v_stack_len NUMBER;
    BEGIN
      SELECT NVL(LENGTH(error_stack), 0) INTO v_stack_len
      FROM app_error_log
      WHERE module = 'process_refund'
      ORDER BY created_at DESC
      FETCH FIRST 1 ROWS ONLY;

      IF v_stack_len > 10 THEN
        DBMS_OUTPUT.PUT_LINE('PASS: WHEN OTHERS logged with non-empty stack');
      ELSE
        DBMS_OUTPUT.PUT_LINE('FAIL: log entry has empty or short stack (' || v_stack_len || ' chars)');
      END IF;
    END;
  ELSE
    DBMS_OUTPUT.PUT_LINE('FAIL: no log entry created');
  END IF;
END test_when_others_logs;
/

-- ============================================================
-- Test 3: Batch skip-and-continue
-- ============================================================

CREATE OR REPLACE PROCEDURE test_batch_skip IS
  v_ok  NUMBER;
  v_err NUMBER;
BEGIN
  -- Setup: 5 queue items, item 3 has non-existent account
  DELETE FROM payment_queue WHERE queue_id BETWEEN 90001 AND 90005;
  INSERT INTO payment_queue (queue_id, account_id, amount, status)
  SELECT 90000 + level,
         CASE WHEN level = 3 THEN -1 ELSE level END,
         100, 'PENDING'
  FROM dual CONNECT BY level <= 5;
  COMMIT;

  -- Ensure test accounts exist (except -1)
  FOR i IN 1..5 LOOP
    IF i != 3 THEN
      BEGIN
        INSERT INTO accounts (account_id, balance, status)
        VALUES (i, 10000, 'ACTIVE');
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;
    END IF;
  END LOOP;
  COMMIT;

  -- Run batch
  run_daily_payments;

  -- Verify results
  SELECT
    COUNT(CASE WHEN status = 'DONE' THEN 1 END),
    COUNT(CASE WHEN status = 'FAILED' THEN 1 END)
  INTO v_ok, v_err
  FROM payment_queue
  WHERE queue_id BETWEEN 90001 AND 90005;

  IF v_ok = 4 AND v_err = 1 THEN
    DBMS_OUTPUT.PUT_LINE('PASS: batch processed 4, skipped 1 failure');
  ELSE
    DBMS_OUTPUT.PUT_LINE('FAIL: expected 4 ok + 1 err, got ' || v_ok || ' ok + ' || v_err || ' err');
  END IF;

  -- Cleanup
  DELETE FROM payment_queue WHERE queue_id BETWEEN 90001 AND 90005;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DELETE FROM payment_queue WHERE queue_id BETWEEN 90001 AND 90005;
    COMMIT;
    RAISE;
END test_batch_skip;
/

-- ============================================================
-- Run all tests
-- ============================================================

BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Exception Handler Tests ===');
  DBMS_OUTPUT.PUT_LINE('');
  test_not_found_raises;
  test_when_others_logs;
  test_batch_skip;
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('=== All tests complete ===');
END;
/