ORA-06512 — Read Bottom-Up to Avoid 9-Day Misdiagnosis

-- Demonstrates a nested call producing a full error stack
-- Run this in SQL*Plus, SQLcl, or any Oracle client
-- Tested on Oracle 19c, 21c, and 23ai

CREATE OR REPLACE PACKAGE stack_demo_pkg AS
  PROCEDURE outer_caller;
  PROCEDURE middle_caller;
  PROCEDURE inner_caller;
END stack_demo_pkg;
/

CREATE OR REPLACE PACKAGE BODY stack_demo_pkg AS

  PROCEDURE inner_caller IS
    v_dummy VARCHAR2(1);
  BEGIN
    -- This raises ORA-01403: no data found
    -- SELECT INTO with no aggregate on a query returning zero rows
    -- COUNT(*) would NOT raise this error — it always returns one row
    SELECT dummy INTO v_dummy
    FROM dual
    WHERE 1 = 0;
  END inner_caller;

  PROCEDURE middle_caller IS
  BEGIN
    inner_caller;  -- Calls inner_caller at this line
  END middle_caller;

  PROCEDURE outer_caller IS
  BEGIN
    middle_caller;  -- Calls middle_caller at this line
  END outer_caller;

END stack_demo_pkg;
/

-- Execute and observe the error stack
BEGIN
  stack_demo_pkg.outer_caller;
END;
/

/*
Expected error stack (read BOTTOM-UP):

ORA-01403: no data found                              <- REAL ERROR: what failed
ORA-06512: at "DEMO.STACK_DEMO_PKG", line 8          <- inner_caller: SELECT INTO
ORA-06512: at "DEMO.STACK_DEMO_PKG", line 16         <- middle_caller: called inner
ORA-06512: at "DEMO.STACK_DEMO_PKG", line 21         <- outer_caller: called middle
ORA-06512: at line 2                                   <- anonymous block

Reading order:
1. ORA-01403 is the actual error — no data found from a SELECT INTO
2. Line 8 (inner_caller) is WHERE it failed — investigate here first
3. Line 16 (middle_caller) is the intermediate caller
4. Line 21 (outer_caller) is the outermost PL/SQL caller
5. "at line 2" is the anonymous block that started execution

Common mistake: investigating line 21 first because it appears at the top.
Correct approach: investigate line 8 first because it is the deepest call.
*/

-- ============================================================
-- Step 1: Retrieve source code at the ORA-06512 line number
-- Replace OWNER, PKG_NAME, and line numbers with values from your error stack
-- ============================================================

SELECT
  line,
  text
FROM all_source
WHERE owner = 'RECON'           -- Schema from ORA-06512 (before the dot)
  AND name  = 'LEDGER_PKG'      -- Object from ORA-06512 (after the dot)
  AND type  = 'PACKAGE BODY'    -- Always PACKAGE BODY for package procedures
  AND line BETWEEN 49 AND 55    -- Line 52 +/- 3 for context
ORDER BY line;

/*
Example output:
  LINE  TEXT
  ----  ------------------------------------------------
  49    PROCEDURE get_ledger_balance(p_account_id IN NUMBER) IS
  50      v_balance NUMBER;
  51    BEGIN
  52      SELECT balance INTO v_balance          <- ORA-06512 points here
  53      FROM ledger_entries_v
  54      WHERE account_id = p_account_id;
  55    ...

Now you know: the SELECT INTO at line 52 against ledger_entries_v
is returning zero rows for the given account_id.
*/

-- ============================================================
-- Step 2: Check when the object was last compiled
-- If last_ddl_time > error timestamp, line numbers may be stale
-- ============================================================

SELECT
  object_name,
  object_type,
  status,
  last_ddl_time,
  created
FROM all_objects
WHERE object_name = 'LEDGER_PKG'
  AND object_type = 'PACKAGE BODY';

/*
If last_ddl_time is AFTER the error timestamp:
- The object was recompiled since the error occurred
- The line numbers in the error stack reference the OLD compilation
- Reproduce the error to get a fresh stack with current line numbers
*/

-- ============================================================
-- Step 3: Retrieve source for ALL ORA-06512 lines in one query
-- Useful when the stack has 3+ frames
-- ============================================================

SELECT
  s.name AS object_name,
  s.line,
  TRIM(s.text) AS source_line
FROM all_source s
WHERE s.type = 'PACKAGE BODY'
  AND (
    (s.name = 'RECON_BATCH_PKG' AND s.line BETWEEN 845 AND 849)
    OR (s.name = 'LEDGER_PKG' AND s.line BETWEEN 201 AND 205)
    OR (s.name = 'LEDGER_PKG' AND s.line BETWEEN 50 AND 54)
  )
ORDER BY
  CASE s.name WHEN 'LEDGER_PKG' THEN 1 ELSE 2 END,  -- Deepest first
  s.line;

-- ============================================================
-- Step 4: Find all WHEN OTHERS handlers in the call chain
-- ============================================================

SELECT
  name,
  line,
  TRIM(text) AS handler_code
FROM all_source
WHERE name IN ('RECON_BATCH_PKG', 'LEDGER_PKG')
  AND type = 'PACKAGE BODY'
  AND UPPER(text) LIKE '%WHEN OTHERS%'
ORDER BY name, line;

-- ============================================================
-- Error log table
-- ============================================================

CREATE TABLE app_error_log (
  log_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  module        VARCHAR2(200)  NOT NULL,
  error_code    NUMBER,
  error_stack   VARCHAR2(4000),
  backtrace     VARCHAR2(4000),
  call_stack    VARCHAR2(4000),
  context_info  VARCHAR2(4000),
  created_at    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE INDEX idx_error_log_created ON app_error_log(created_at DESC);
CREATE INDEX idx_error_log_module  ON app_error_log(module);

-- ============================================================
-- Error logging package with AUTONOMOUS_TRANSACTION
-- ============================================================

CREATE OR REPLACE PACKAGE error_handler_pkg AS

  -- Log the full error context and re-raise the original exception
  PROCEDURE log_and_reraise(
    p_module       IN VARCHAR2,
    p_context_info IN VARCHAR2 DEFAULT NULL
  );

  -- Log the full error context and raise a custom error with business context
  PROCEDURE log_and_raise_custom(
    p_module  IN VARCHAR2,
    p_message IN VARCHAR2,
    p_errno   IN NUMBER DEFAULT -20000
  );

END error_handler_pkg;
/

CREATE OR REPLACE PACKAGE BODY error_handler_pkg AS

  -- Private: autonomous transaction logging
  -- Commits the log entry independently of the caller's transaction
  -- If the caller rolls back, the error log entry is preserved
  PROCEDURE write_log(
    p_module       IN VARCHAR2,
    p_error_code   IN NUMBER,
    p_error_stack  IN VARCHAR2,
    p_backtrace    IN VARCHAR2,
    p_call_stack   IN VARCHAR2,
    p_context_info IN VARCHAR2
  ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO app_error_log (
      module,
      error_code,
      error_stack,
      backtrace,
      call_stack,
      context_info
    ) VALUES (
      p_module,
      p_error_code,
      p_error_stack,
      p_backtrace,
      p_call_stack,
      p_context_info
    );
    COMMIT;  -- Commits only this autonomous transaction
  END write_log;

  PROCEDURE log_and_reraise(
    p_module       IN VARCHAR2,
    p_context_info IN VARCHAR2 DEFAULT NULL
  ) IS
    -- Capture ALL error context FIRST — before any other SQL
    v_error_code  NUMBER         := SQLCODE;
    v_error_stack VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
    v_backtrace   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    v_call_stack  VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;
  BEGIN
    write_log(
      p_module       => p_module,
      p_error_code   => v_error_code,
      p_error_stack  => v_error_stack,
      p_backtrace    => v_backtrace,
      p_call_stack   => v_call_stack,
      p_context_info => p_context_info
    );

    -- Re-raise the original exception — preserves the original error code
    RAISE;
  END log_and_reraise;

  PROCEDURE log_and_raise_custom(
    p_module  IN VARCHAR2,
    p_message IN VARCHAR2,
    p_errno   IN NUMBER DEFAULT -20000
  ) IS
    v_error_code  NUMBER         := SQLCODE;
    v_error_stack VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
    v_backtrace   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    v_call_stack  VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;
  BEGIN
    write_log(
      p_module       => p_module,
      p_error_code   => v_error_code,
      p_error_stack  => v_error_stack,
      p_backtrace    => v_backtrace,
      p_call_stack   => v_call_stack,
      p_context_info => p_message
    );

    -- TRUE as third parameter preserves the original error chain
    RAISE_APPLICATION_ERROR(
      p_errno,
      p_message || ' | Original: ' || SUBSTR(v_error_stack, 1, 1000),
      TRUE  -- keep_errors: preserves the original error stack
    );
  END log_and_raise_custom;

END error_handler_pkg;
/

-- ============================================================
-- Usage in application code
-- ============================================================

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: log with business context and raise custom error
    error_handler_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: log everything and re-raise the original error
    error_handler_pkg.log_and_reraise(
      p_module       => 'charge_account',
      p_context_info => 'account_id=' || p_account_id || ', amount=' || p_amount
    );
END charge_account;
/

-- Demonstrates correct order and content of each error capture function

CREATE OR REPLACE PROCEDURE error_capture_demo IS
  v_dummy VARCHAR2(1);
BEGIN
  -- Force an error: query a non-existent table
  EXECUTE IMMEDIATE 'SELECT dummy FROM non_existent_table_xyz' INTO v_dummy;

EXCEPTION
  WHEN OTHERS THEN
    -- STEP 1: Capture ALL error context into variables FIRST
    -- Do NOT execute any SQL before these assignments
    DECLARE
      v_sqlcode      NUMBER         := SQLCODE;
      v_sqlerrm      VARCHAR2(4000) := SQLERRM;
      v_error_stack  VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
      v_backtrace    VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      v_call_stack   VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;
    BEGIN
      -- STEP 2: Now safe to output or log
      DBMS_OUTPUT.PUT_LINE('=== SQLCODE ===');
      DBMS_OUTPUT.PUT_LINE(v_sqlcode);
      DBMS_OUTPUT.PUT_LINE('');

      DBMS_OUTPUT.PUT_LINE('=== SQLERRM ===');
      DBMS_OUTPUT.PUT_LINE(v_sqlerrm);
      DBMS_OUTPUT.PUT_LINE('');

      DBMS_OUTPUT.PUT_LINE('=== FORMAT_ERROR_STACK (What failed?) ===');
      DBMS_OUTPUT.PUT_LINE(v_error_stack);

      DBMS_OUTPUT.PUT_LINE('=== FORMAT_ERROR_BACKTRACE (Where did it fail?) ===');
      DBMS_OUTPUT.PUT_LINE(v_backtrace);

      DBMS_OUTPUT.PUT_LINE('=== FORMAT_CALL_STACK (How did we get here?) ===');
      DBMS_OUTPUT.PUT_LINE(v_call_stack);

      -- STEP 3: Re-raise the original error
      RAISE;
    END;
END error_capture_demo;
/

-- Run it
SET SERVEROUTPUT ON
BEGIN
  error_capture_demo;
END;
/

/*
Expected output:

=== SQLCODE ===
-942

=== SQLERRM ===
ORA-00942: table or view does not exist

=== FORMAT_ERROR_STACK (What failed?) ===
ORA-00942: table or view does not exist

=== FORMAT_ERROR_BACKTRACE (Where did it fail?) ===
ORA-06512: at "DEMO.ERROR_CAPTURE_DEMO", line 5

=== FORMAT_CALL_STACK (How did we get here?) ===
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
  0x...          12  procedure DEMO.ERROR_CAPTURE_DEMO
  0x...           2  anonymous block

Note:
- FORMAT_ERROR_STACK returns the error code and message
- FORMAT_ERROR_BACKTRACE returns the ORA-06512 call chain
- FORMAT_CALL_STACK returns the full call stack including non-error frames
- SQLCODE returns the numeric error code (note: +100 for NO_DATA_FOUND)
*/

-- UTL_CALL_STACK demo — programmatic stack frame access
-- Requires Oracle 12c or later (19c, 21c, 23ai)

CREATE OR REPLACE PACKAGE utl_stack_demo_pkg AS
  PROCEDURE outer_proc;
  PROCEDURE inner_proc;
END utl_stack_demo_pkg;
/

CREATE OR REPLACE PACKAGE BODY utl_stack_demo_pkg AS

  PROCEDURE inner_proc IS
    v_dummy VARCHAR2(1);
  BEGIN
    -- Force ORA-01403
    SELECT dummy INTO v_dummy FROM dual WHERE 1 = 0;
  END inner_proc;

  PROCEDURE outer_proc IS
  BEGIN
    inner_proc;
  EXCEPTION
    WHEN OTHERS THEN
      -- ===== UTL_CALL_STACK: Error Backtrace =====
      -- Iterate over backtrace frames programmatically
      -- No string parsing required
      DBMS_OUTPUT.PUT_LINE('=== Error Backtrace (UTL_CALL_STACK) ===');
      DBMS_OUTPUT.PUT_LINE('Backtrace depth: ' || UTL_CALL_STACK.BACKTRACE_DEPTH);
      DBMS_OUTPUT.PUT_LINE('');

      FOR i IN 1 .. UTL_CALL_STACK.BACKTRACE_DEPTH LOOP
        DBMS_OUTPUT.PUT_LINE(
          'Frame ' || i || ': ' ||
          'Line ' || UTL_CALL_STACK.BACKTRACE_LINE(i) || ' of ' ||
          NVL(UTL_CALL_STACK.BACKTRACE_UNIT(i), 'anonymous block')
        );
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('');

      -- ===== UTL_CALL_STACK: Current Call Stack =====
      DBMS_OUTPUT.PUT_LINE('=== Current Call Stack (UTL_CALL_STACK) ===');
      DBMS_OUTPUT.PUT_LINE('Dynamic depth: ' || UTL_CALL_STACK.DYNAMIC_DEPTH);
      DBMS_OUTPUT.PUT_LINE('');

      FOR i IN 1 .. UTL_CALL_STACK.DYNAMIC_DEPTH LOOP
        BEGIN
          DBMS_OUTPUT.PUT_LINE(
            'Frame ' || i || ': ' ||
            'Line ' || UTL_CALL_STACK.UNIT_LINE(i) || ' of ' ||
            NVL(UTL_CALL_STACK.CONCATENATE_SUBPROGRAM(i), 'anonymous block')
          );
        EXCEPTION
          WHEN OTHERS THEN
            -- Some frames may not have unit information
            DBMS_OUTPUT.PUT_LINE(
              'Frame ' || i || ': ' ||
              'Line ' || UTL_CALL_STACK.UNIT_LINE(i) || ' (no unit info)'
            );
        END;
      END LOOP;

      RAISE;
  END outer_proc;

END utl_stack_demo_pkg;
/

SET SERVEROUTPUT ON
BEGIN
  utl_stack_demo_pkg.outer_proc;
END;
/

/*
Expected output:

=== Error Backtrace (UTL_CALL_STACK) ===
Backtrace depth: 2

Frame 1: Line 7 of DEMO.UTL_STACK_DEMO_PKG     <- inner_proc (deepest)
Frame 2: Line 13 of DEMO.UTL_STACK_DEMO_PKG    <- outer_proc (shallowest)

=== Current Call Stack (UTL_CALL_STACK) ===
Dynamic depth: 3

Frame 1: Line 18 of DEMO.UTL_STACK_DEMO_PKG.OUTER_PROC
Frame 2: Line 2 of anonymous block
Frame 3: ... (Oracle internal frames)

Key differences from DBMS_UTILITY:
- Individual frame access via function calls — no string parsing
- BACKTRACE_UNIT returns the object name directly
- CONCATENATE_SUBPROGRAM returns the fully qualified name
  including package and procedure (e.g., PKG_NAME.PROC_NAME)
- Frame 1 is the deepest call, Frame N is the shallowest
*/

-- ============================================================
-- Centralized error code constants and helper procedures
-- ============================================================

CREATE OR REPLACE PACKAGE app_error_pkg AS

  -- Error code ranges — documented and enforced
  c_validation_err   CONSTANT NUMBER := -20001;
  c_not_found_err    CONSTANT NUMBER := -20010;
  c_auth_err         CONSTANT NUMBER := -20020;
  c_business_rule    CONSTANT NUMBER := -20030;
  c_system_err       CONSTANT NUMBER := -20100;

  -- Raise a validation error with field context
  PROCEDURE raise_validation(
    p_field   IN VARCHAR2,
    p_message IN VARCHAR2
  );

  -- Raise a not-found error with entity context
  PROCEDURE raise_not_found(
    p_entity IN VARCHAR2,
    p_id     IN VARCHAR2
  );

  -- Re-raise a caught exception with business context
  -- Preserves the original error chain (keep_errors = TRUE)
  PROCEDURE reraise_with_context(
    p_context IN VARCHAR2
  );

END app_error_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_error_pkg AS

  PROCEDURE raise_validation(
    p_field   IN VARCHAR2,
    p_message IN VARCHAR2
  ) IS
  BEGIN
    RAISE_APPLICATION_ERROR(
      c_validation_err,
      'Validation failed [' || p_field || ']: ' || p_message
    );
    -- keep_errors defaults to FALSE — this is a new error, not a re-raise
  END raise_validation;

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

  PROCEDURE reraise_with_context(
    p_context IN VARCHAR2
  ) IS
    v_original VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
  BEGIN
    -- TRUE = keep_errors: preserves the original error chain in the stack
    -- The caller sees both the custom message and the original ORA-XXXXX
    RAISE_APPLICATION_ERROR(
      c_system_err,
      p_context || ' | Original: ' || SUBSTR(v_original, 1, 800),
      TRUE  -- Preserve original error stack
    );
  END reraise_with_context;

END app_error_pkg;
/

-- ============================================================
-- Usage: calling code handles errors by code
-- ============================================================

BEGIN
  charge_account(99999, 100);
EXCEPTION
  WHEN OTHERS THEN
    CASE SQLCODE
      WHEN app_error_pkg.c_not_found_err THEN
        DBMS_OUTPUT.PUT_LINE('Account not found — routing to creation flow');
      WHEN app_error_pkg.c_validation_err THEN
        DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM);
      WHEN app_error_pkg.c_business_rule THEN
        DBMS_OUTPUT.PUT_LINE('Business rule violation: ' || SQLERRM);
      ELSE
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        RAISE;  -- Re-raise unknown errors — do not swallow
    END CASE;
END;
/

============================================================
JAVA / JDBC
============================================================

try {
    callableStatement.execute();
} catch (SQLException e) {
    // Error code: numeric ORA code without the ORA- prefix
    int oracleErrorCode = e.getErrorCode();     // e.g., 1403

    // Full message: includes ORA-XXXXX + ORA-06512 chain
    String message = e.getMessage();
    // "ORA-01403: no data found\nORA-06512: at \"RECON.LEDGER_PKG\", line 52\n..."

    // SQL state: Oracle-specific state code
    String sqlState = e.getSQLState();           // e.g., "42000"

    // For chained exceptions (rare in Oracle JDBC):
    SQLException next = e.getNextException();
}


============================================================
PYTHON / python-oracledb (cx_Oracle successor)
============================================================

import oracledb

try:
    cursor.callproc('charge_account', [99999, 100])
except oracledb.DatabaseError as e:
    error_obj = e.args[0]

    # Error code: numeric ORA code
    error_code = error_obj.code          # e.g., 1403

    # Full message: includes ORA-XXXXX + ORA-06512 chain
    error_message = error_obj.message
    # 'ORA-01403: no data found\nORA-06512: at "RECON.LEDGER_PKG", line 52\n...'

    # Offset (for SQL parse errors): character position of the error
    error_offset = error_obj.offset      # e.g., 0

    # For structured logging:
    logger.error(
        'Oracle error',
        extra={
            'ora_code': error_code,
            'ora_message': error_message,
            'procedure': 'charge_account',
            'params': {'account_id': 99999, 'amount': 100}
        }
    )


============================================================
NODE.JS / node-oracledb
============================================================

const oracledb = require('oracledb');

try {
    await connection.execute('BEGIN charge_account(:id, :amt); END;',
        { id: 99999, amt: 100 });
} catch (err) {
    // Error code: numeric ORA code
    const errorCode = err.errorNum;      // e.g., 1403

    // Full message: includes ORA-XXXXX + ORA-06512 chain
    const message = err.message;
    // 'ORA-01403: no data found\nORA-06512: at "RECON.LEDGER_PKG", line 52\n...'

    // Offset (for SQL parse errors)
    const offset = err.offset;           // e.g., 0

    // For structured logging:
    console.error(JSON.stringify({
        oraCode: errorCode,
        oraMessage: message,
        procedure: 'charge_account',
        params: { accountId: 99999, amount: 100 }
    }));
}


============================================================
Key points across all drivers:
============================================================
1. The full ORA-XXXXX + ORA-06512 chain is in the message string
2. The numeric error code is available as a separate property
3. SQLCODE +100 (NO_DATA_FOUND) maps to error code 1403 in drivers
4. ORA-20000 to ORA-20999 (RAISE_APPLICATION_ERROR) appear with
   the custom message text as the driver's error message
5. Parse ORA-XXXXX from the message for structured logging —
   include it as a separate field to enable filtering by error code

-- ============================================================
-- PRODUCTION INCIDENT ANALYSIS WORKFLOW
-- Run these queries in order during an active incident
-- ============================================================

-- Step 1: Find recent errors in the application error log

SELECT
  log_id,
  module,
  error_code,
  SUBSTR(error_stack, 1, 200) AS error_preview,
  created_at
FROM app_error_log
WHERE created_at > SYSTIMESTAMP - INTERVAL '1' HOUR
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

-- Step 2: Get the full error stack for a specific log entry

SELECT
  error_stack,
  backtrace,
  call_stack,
  context_info
FROM app_error_log
WHERE log_id = :log_id_from_step_1;

-- Step 3: Map ALL ORA-06512 line numbers to source code
-- Update the object names and line numbers from the backtrace

SELECT
  s.name AS object_name,
  s.line,
  TRIM(s.text) AS source_line
FROM all_source s
WHERE s.type = 'PACKAGE BODY'
  AND (
    (s.name = 'RECON_BATCH_PKG' AND s.line BETWEEN 845 AND 849)
    OR (s.name = 'LEDGER_PKG' AND s.line BETWEEN 201 AND 205)
    OR (s.name = 'LEDGER_PKG' AND s.line BETWEEN 50 AND 54)
  )
ORDER BY
  CASE s.name
    WHEN 'LEDGER_PKG' THEN 1
    WHEN 'RECON_BATCH_PKG' THEN 2
    ELSE 3
  END,
  s.line;

-- Step 4: Verify compilation timestamps
-- If last_ddl_time > error timestamp, line numbers are stale

SELECT
  object_name,
  object_type,
  status,
  last_ddl_time
FROM all_objects
WHERE object_name IN ('RECON_BATCH_PKG', 'LEDGER_PKG')
  AND object_type = 'PACKAGE BODY'
ORDER BY object_name;

-- Step 5: Find all WHEN OTHERS handlers in the call chain

SELECT
  name,
  line,
  TRIM(text) AS handler_code
FROM all_source
WHERE name IN ('RECON_BATCH_PKG', 'LEDGER_PKG')
  AND type = 'PACKAGE BODY'
  AND UPPER(text) LIKE '%WHEN OTHERS%'
ORDER BY name, line;

-- Step 6: Trace the data at the failing SELECT INTO
-- If ORA-01403 on ledger_entries_v:

SELECT account_type, COUNT(*) AS entry_count
FROM ledger_entries_v
GROUP BY account_type
ORDER BY account_type;

-- Compare with the full accounts table
SELECT account_type, COUNT(*) AS account_count
FROM accounts
GROUP BY account_type
ORDER BY account_type;

-- Missing account_type in the view is likely the root cause
-- Fix the view filter, then add a pre-flight validation check