SQL Developer Debugging Tutorial – Catch ORA-06512 Faster

-- ===================================================
-- Step 1: Grant debugging privileges (run as DBA)
-- ===================================================

-- Option A: Full debug access (appropriate for senior developers)
GRANT DEBUG CONNECT SESSION TO your_developer_user;
GRANT DEBUG ANY PROCEDURE TO your_developer_user;

-- Option B: Least-privilege debug access (specific objects only)
GRANT DEBUG CONNECT SESSION TO your_developer_user;
GRANT DEBUG ON io.thecodeforge.report.generator TO your_developer_user;
GRANT DEBUG ON io.thecodeforge.report.batch_runner TO your_developer_user;
GRANT DEBUG ON io.thecodeforge.scheduler.nightly_job TO your_developer_user;

-- Verify privileges were granted
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'YOUR_DEVELOPER_USER'
  AND privilege IN ('DEBUG CONNECT SESSION', 'DEBUG ANY PROCEDURE')
ORDER BY privilege;

-- ===================================================
-- Step 2: Compile target objects with DEBUG flag
-- Run for every object in the call chain
-- ===================================================

-- Compile individual procedures
ALTER PROCEDURE io.thecodeforge.payment.reconcile_batch COMPILE DEBUG;
ALTER PROCEDURE io.thecodeforge.scheduler.nightly_job COMPILE DEBUG;

-- Compile packages: spec first, then body
-- The body is where procedure code lives — the body must have DEBUG
ALTER PACKAGE io.thecodeforge.report.generator COMPILE DEBUG;
ALTER PACKAGE io.thecodeforge.report.generator COMPILE BODY DEBUG;

ALTER PACKAGE io.thecodeforge.report.batch_runner COMPILE DEBUG;
ALTER PACKAGE io.thecodeforge.report.batch_runner COMPILE BODY DEBUG;

-- ===================================================
-- Step 3: Verify DEBUG compilation status
-- ===================================================
SELECT p.object_name,
       p.object_type,
       p.procedure_name,
       CASE WHEN p.debuginfo = 'YES'
            THEN 'DEBUG ENABLED — breakpoints will work'
            ELSE 'DEBUG MISSING — recompile with DEBUG flag'
       END AS debug_status
FROM user_procedures p
WHERE p.object_name IN (
  'RECONCILE_BATCH',
  'GENERATOR',
  'BATCH_RUNNER',
  'NIGHTLY_JOB'
)
ORDER BY p.object_name, p.procedure_name;

-- ===================================================
-- Step 4: Revert to non-debug compilation
-- ALWAYS run this before promoting to any environment
-- beyond your local dev schema
-- ===================================================
ALTER PROCEDURE io.thecodeforge.payment.reconcile_batch COMPILE;
ALTER PACKAGE io.thecodeforge.report.generator COMPILE;
ALTER PACKAGE io.thecodeforge.report.generator COMPILE BODY;
ALTER PACKAGE io.thecodeforge.report.batch_runner COMPILE;
ALTER PACKAGE io.thecodeforge.report.batch_runner COMPILE BODY;
ALTER PROCEDURE io.thecodeforge.scheduler.nightly_job COMPILE;

-- Verify revert: all should show 'DEBUG MISSING' after revert
SELECT object_name, object_type,
       CASE WHEN debuginfo = 'YES'
            THEN 'WARNING: STILL IN DEBUG MODE — DO NOT DEPLOY'
            ELSE 'OK: Non-debug compilation confirmed'
       END AS deployment_status
FROM user_procedures
WHERE object_name IN (
  'RECONCILE_BATCH', 'GENERATOR', 'BATCH_RUNNER', 'NIGHTLY_JOB'
)
ORDER BY object_name;

-- ===================================================
-- SQL Developer Exception Breakpoint Setup
-- UI Path: View > Breakpoints > right-click > Add Exception Breakpoint
-- ===================================================

-- Common error numbers for exception breakpoints:
-- Enter these in the 'Error Number' field (without ORA- prefix):
--
--   1403  — ORA-01403: NO_DATA_FOUND (SELECT INTO returned no rows)
--   1     — ORA-00001: unique constraint violated
--   1422  — ORA-01422: TOO_MANY_ROWS (SELECT INTO returned multiple rows)
--   6502  — ORA-06502: VALUE_ERROR (type conversion failure)
--   1722  — ORA-01722: invalid number (implicit conversion failure)
--   2291  — ORA-02291: FK parent key not found
--   60    — ORA-00060: deadlock detected
--   1555  — ORA-01555: snapshot too old

-- ===================================================
-- Execution block: run this to trigger the debug session
-- The exception breakpoint will fire when the error occurs
-- ===================================================
DECLARE
  -- Declare any test variables needed
  v_batch_id  NUMBER := 12345;
  v_run_date  DATE   := DATE '2026-04-14';
BEGIN
  -- For remote debugging: connect SQL Developer first, then call CONNECT_TCP
  -- For local debugging: skip CONNECT_TCP and just call the procedure
  
  -- Call the failing procedure — debugger halts when exception fires
  io.thecodeforge.payment.reconcile_batch(
    p_batch_id => v_batch_id,
    p_run_date => v_run_date
  );
END;
/

-- ===================================================
-- What you see when the exception breakpoint fires:
-- ===================================================
-- 1. Execution halts at the EXACT line that raised ORA-01403
--    (e.g., line 142 in io.thecodeforge.report.generator)
-- 2. That line is highlighted in the source editor
-- 3. Variables window shows all in-scope variables:
--    p_report_type = 'QUARTERLY_ADJUSTMENT'  <-- the problem
--    p_run_date    = 2026-04-14
--    v_count       = 0                        <-- why NO_DATA_FOUND
-- 4. Call Stack window shows:
--    Frame 0: IO.THECODEFORGE.REPORT.GENERATOR.GET_TEMPLATE  line 142
--    Frame 1: IO.THECODEFORGE.REPORT.BATCH_RUNNER.RUN        line 87
--    Frame 2: IO.THECODEFORGE.SCHEDULER.NIGHTLY_JOB.EXECUTE  line 23
--
-- Click Frame 1 -> jumps to batch_runner line 87,
--   Variables window updates to show batch_runner's local variables
-- Click Frame 2 -> jumps to nightly_job line 23,
--   Variables window updates to show scheduler's local variables

-- ===================================================
-- Conditional exception breakpoint (for intermittent errors)
-- Set condition in Breakpoints > Properties > Condition:
-- ===================================================
-- Condition expression examples (enter in Breakpoints Properties dialog):
--   p_report_type = 'QUARTERLY_ADJUSTMENT'   -- only fire for this report type
--   p_batch_id > 10000                        -- only fire for large batch IDs
--   v_count = 0                               -- only fire when count is zero
--   p_customer_id IS NULL                     -- only fire for NULL customer

-- ===================================================
-- Textual ORA-06512 stack (what you see WITHOUT the debugger)
-- ===================================================
-- ORA-01403: no data found
-- ORA-06512: at "IO.THECODEFORGE.REPORT.GENERATOR", line 142
-- ORA-06512: at "IO.THECODEFORGE.REPORT.BATCH_RUNNER", line 87
-- ORA-06512: at "IO.THECODEFORGE.SCHEDULER.NIGHTLY_JOB", line 23
-- ORA-06512: at line 1
--
-- Manual work required:
-- 1. Open io.thecodeforge.report.generator source
-- 2. Navigate to line 142
-- 3. Guess what the variable values were
-- 4. Open io.thecodeforge.report.batch_runner, go to line 87
-- 5. Guess what was passed as arguments
-- 6. Repeat for nightly_job line 23
-- 7. Make a change, redeploy, re-run the batch, wait 40 minutes
-- Total: 2–4 hours per debugging cycle

-- ===================================================
-- Call Stack window (what you see WITH the debugger)
-- ===================================================
-- Frame 0: IO.THECODEFORGE.REPORT.GENERATOR.GET_TEMPLATE    line 142
--          [highlighted in source editor — this is where execution halted]
-- Frame 1: IO.THECODEFORGE.REPORT.BATCH_RUNNER.PROCESS_TYPE line 87
-- Frame 2: IO.THECODEFORGE.SCHEDULER.NIGHTLY_JOB.EXECUTE    line 23
-- Frame 3: anonymous block                                   line 5
--
-- Interactive actions:
-- Click Frame 0 -> source editor shows generator line 142,
--                  Variables window shows:
--                    p_report_type = 'QUARTERLY_ADJUSTMENT'
--                    p_run_date    = 2026-04-14
--                    v_template_id = <NULL>   <-- the problem
--
-- Click Frame 1 -> source editor shows batch_runner line 87,
--                  Variables window shows:
--                    v_report_types = ['MONTHLY','QUARTERLY_ADJUSTMENT','WEEKLY']
--                    v_current_type = 'QUARTERLY_ADJUSTMENT'
--                    v_batch_id     = 12345
--
-- Click Frame 2 -> source editor shows nightly_job line 23,
--                  Variables window shows:
--                    v_run_date = 2026-04-14
--                    v_job_name = 'NIGHTLY_REPORT_BATCH'
--
-- Total: 4 minutes from breakpoint fire to root cause identified
-- No redeployment, no re-execution, no guessing

-- ===================================================
-- Read the Call Stack correctly: bottom is the outermost caller
-- ===================================================
-- Frame 3 (anonymous block, line 5)    — the entry point (top-level caller)
--   called Frame 2 (nightly_job, line 23)
--     called Frame 1 (batch_runner, line 87)
--       called Frame 0 (generator, line 142)  — where the error occurred
--
-- This matches the ORA-06512 reading order:
-- The actual error is at the TOP of the ORA-06512 text stack
-- and at Frame 0 (the BOTTOM) of the Call Stack window.
-- Both read: innermost call first, outermost caller last.

-- ===================================================
-- Stepping through the call chain manually
-- (use when exception breakpoint fires at an unexpected location)
-- ===================================================
-- F7  (Step Into)  — enter the next called procedure
-- F8  (Step Over)  — execute the next line without entering sub-calls
-- F9  (Resume)     — continue execution until the next breakpoint
-- Shift+F7 (Step Out) — finish the current procedure and return to caller
--
-- Recommended ORA-06512 debugging sequence:
-- 1. Set exception breakpoint
-- 2. Run the failing code
-- 3. When breakpoint fires, inspect Frame 0 variables
-- 4. Click Frame 1 to understand the calling context
-- 5. Click Frame 2 to understand the entry conditions
-- 6. Root cause is usually apparent within 3 frames

-- ===================================================
-- Watch window expressions for ORA-06512 debugging
-- Add these via: Watch window > right-click > Add Watch
-- ===================================================

-- Universal: current error state
SQLCODE
SQLERRM

-- ORA-01403 (NO_DATA_FOUND): inspect SELECT INTO parameters
p_report_type               -- is this NULL or an unrecognized value?
p_customer_id               -- is this NULL?
v_count                     -- is this 0 when it should be > 0?

-- ORA-01422 (TOO_MANY_ROWS): confirm uniqueness assumption
v_customer_count            -- this should be 1, might be > 1

-- ORA-06502 (VALUE_ERROR): length and type checks
LENGTH(v_string_var)        -- is this longer than the target VARCHAR2?
DUMP(v_string_var)          -- raw bytes — reveals encoding issues
LENGTH(v_description)       -- compare against column definition length

-- ORA-01722 (invalid number): check string content
v_amount_str                -- does this contain '$', ',', or spaces?
REGEXP_REPLACE(v_amount_str, '[^0-9.\-]', '')  -- stripped version

-- ORA-00001 (unique constraint): see the duplicate key values
p_order_id                  -- what value is being inserted as PK?
p_batch_id || '_' || p_seq  -- composite key components

-- Cursor state inspection
v_cursor%ROWCOUNT           -- how many rows fetched so far?
v_cursor%ISOPEN             -- is the cursor still open?
v_collection.COUNT          -- number of elements in collection
v_collection.FIRST          -- first index (NULL if empty)
v_collection.LAST           -- last index

-- Conditional expressions for context
CASE WHEN p_report_type IS NULL THEN 'NULL_INPUT' ELSE p_report_type END
CASE WHEN v_count = 0 THEN 'NO_TEMPLATE_FOUND' ELSE TO_CHAR(v_count) || '_FOUND' END

-- ===================================================
-- Programmatic variable logging for post-mortem analysis
-- Use this pattern when SQL Developer debugger is unavailable
-- (automated batch jobs, production debugging)
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.debug.log_variables(
  p_procedure_name IN VARCHAR2,
  p_context        IN CLOB
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO io.thecodeforge.debug.variable_snapshot (
    snapshot_id,
    procedure_name,
    context_dump,
    call_stack,
    session_id,
    db_user,
    created_at
  ) VALUES (
    io.thecodeforge.debug.snapshot_seq.NEXTVAL,
    p_procedure_name,
    p_context,
    DBMS_UTILITY.FORMAT_CALL_STACK,  -- capture the full stack at log time
    SYS_CONTEXT('USERENV', 'SESSIONID'),
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYSTIMESTAMP
  );
  COMMIT;
END log_variables;
/

-- Usage: add before a suspect SELECT INTO to capture pre-failure state
CREATE OR REPLACE PROCEDURE io.thecodeforge.report.generator(
  p_report_type IN VARCHAR2,
  p_run_date    IN DATE
) IS
  v_template_id NUMBER;
  v_count       NUMBER;
BEGIN
  -- Capture state before the suspect operation
  -- Remove this after debugging — it adds an autonomous transaction per call
  io.thecodeforge.debug.log_variables(
    p_procedure_name => 'GENERATOR.GET_TEMPLATE',
    p_context        =>
      'p_report_type=' || NVL(p_report_type, 'NULL') ||
      ', p_run_date=' || TO_CHAR(p_run_date, 'YYYY-MM-DD') ||
      ', session=' || SYS_CONTEXT('USERENV', 'SESSIONID')
  );
  
  -- The suspect SELECT INTO
  SELECT template_id INTO v_template_id
  FROM io.thecodeforge.report.templates
  WHERE report_type = p_report_type    -- p_report_type = 'QUARTERLY_ADJUSTMENT'
    AND effective_date <= p_run_date   -- and this date comparison
    AND active_flag = 'Y';
  
  -- ... rest of procedure ...
  NULL;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20103,
      'No active template found for report type: ' ||
      NVL(p_report_type, 'NULL') ||
      ' as of ' || TO_CHAR(p_run_date, 'YYYY-MM-DD'));
END generator;
/

-- ===================================================
-- Remote debugging setup: overview of the connection flow
-- ===================================================
--
-- 1. SQL Developer (your workstation) starts listening on port 4000
--    Menu: Run > Remote Debug... > enter listening port > OK
--    SQL Developer is now waiting for an inbound JDWP connection
--
-- 2. Target database session calls CONNECT_TCP pointing at SQL Developer
--    The call BLOCKS until SQL Developer attaches or timeout expires
--
-- 3. SQL Developer attaches to the waiting session
--    The blocked CONNECT_TCP call returns, execution continues
--
-- 4. The failing procedure is called — exception breakpoints and line
--    breakpoints now work as in local debugging
--
-- Network requirement:
--   Database server ---outbound TCP 4000---> SQL Developer workstation
--   (database server initiates; SQL Developer listens)

-- ===================================================
-- Step 1: In SQL Developer, start the remote debug listener
-- Run > Remote Debug > Listening Port: 4000 > OK
-- Status bar shows: "Waiting for remote debug connection..."
-- ===================================================

-- ===================================================
-- Step 2: In the TARGET database session, run this block
-- Replace the host with your SQL Developer machine IP or hostname
-- ===================================================
DECLARE
  v_batch_id NUMBER := 12345;
  v_run_date DATE   := DATE '2026-04-14';
BEGIN
  -- Initiate debug connection from database to SQL Developer
  -- This BLOCKS until SQL Developer attaches (or timeout expires)
  DBMS_DEBUG_JDWP.CONNECT_TCP(
    host    => '10.0.1.45',   -- SQL Developer host IP or hostname
    port    => 4000,           -- must match SQL Developer listening port
    timeout => 60              -- seconds to wait before giving up
  );
  
  -- CONNECT_TCP returned — SQL Developer is now attached
  -- Breakpoints are now active. Call the failing procedure:
  io.thecodeforge.payment.reconcile_batch(
    p_batch_id => v_batch_id,
    p_run_date => v_run_date
  );
  
  -- Optionally disconnect after debugging is complete
  -- DBMS_DEBUG_JDWP.DISCONNECT;
END;
/

-- ===================================================
-- Verify JDWP privilege (required on Oracle 12c+)
-- ===================================================
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'YOUR_USER'
  AND privilege LIKE '%JDWP%';

-- Grant if missing (run as DBA)
GRANT JDWP TO your_developer_user;

-- ===================================================
-- Troubleshooting: test port reachability from DB server
-- (run from OS on the database server, not from SQL*Plus)
-- ===================================================
-- Linux/Unix:
--   nc -zv your-sqldeveloper-host.example.com 4000
--   telnet your-sqldeveloper-host.example.com 4000
--
-- Windows (PowerShell):
--   Test-NetConnection -ComputerName your-sqldeveloper-host -Port 4000

-- ===================================================
-- Defensive wrapper: attempt debug attach, fall back gracefully
-- Useful in test automation where debugger attachment is optional
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.debug.try_attach(
  p_debug_host IN VARCHAR2 DEFAULT 'localhost',
  p_debug_port IN NUMBER   DEFAULT 4000,
  p_timeout    IN NUMBER   DEFAULT 15
) IS
BEGIN
  DBMS_DEBUG_JDWP.CONNECT_TCP(
    host    => p_debug_host,
    port    => p_debug_port,
    timeout => p_timeout
  );
  DBMS_OUTPUT.PUT_LINE(
    'Debug session attached: ' || p_debug_host || ':' || p_debug_port
  );
EXCEPTION
  WHEN OTHERS THEN
    -- Timeout or connection refused — continue without debugger
    DBMS_OUTPUT.PUT_LINE(
      'Debug attach failed (' || SQLERRM || ') — running without debugger'
    );
END try_attach;
/

-- ===================================================
-- Error log table: production stack capture schema
-- ===================================================
CREATE TABLE io.thecodeforge.logging.error_log (
  error_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  module_name     VARCHAR2(128)  NOT NULL,
  error_code      NUMBER,
  error_message   CLOB,
  call_stack      CLOB,   -- DBMS_UTILITY.FORMAT_CALL_STACK
  error_stack     CLOB,   -- DBMS_UTILITY.FORMAT_ERROR_STACK
  error_backtrace CLOB,   -- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
  parameters      CLOB,   -- input values at time of error
  session_id      NUMBER,
  os_user         VARCHAR2(128),
  db_user         VARCHAR2(128),
  client_info     VARCHAR2(64),
  module_ctx      VARCHAR2(64),
  action_ctx      VARCHAR2(64),
  created_at      TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Index for common query patterns
CREATE INDEX io.thecodeforge.logging.err_log_code_ts_idx
  ON io.thecodeforge.logging.error_log (error_code, created_at DESC);

CREATE INDEX io.thecodeforge.logging.err_log_module_ts_idx
  ON io.thecodeforge.logging.error_log (module_name, created_at DESC);

-- ===================================================
-- Centralized error capture procedure
-- Call this from EVERY exception handler before re-raising
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.logging.capture_error(
  p_module     IN VARCHAR2,
  p_parameters IN CLOB DEFAULT NULL
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- survives main transaction rollback
BEGIN
  INSERT INTO io.thecodeforge.logging.error_log (
    module_name,
    error_code,
    error_message,
    call_stack,
    error_stack,
    error_backtrace,
    parameters,
    session_id,
    os_user,
    db_user,
    client_info,
    module_ctx,
    action_ctx,
    created_at
  ) VALUES (
    p_module,
    SQLCODE,
    SQLERRM,
    DBMS_UTILITY.FORMAT_CALL_STACK,      -- who called whom at error time
    DBMS_UTILITY.FORMAT_ERROR_STACK,     -- ORA codes and messages (up to 2000 bytes)
    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, -- original line numbers preserved through re-raises
    p_parameters,
    SYS_CONTEXT('USERENV', 'SESSIONID'),
    SYS_CONTEXT('USERENV', 'OS_USER'),
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYS_CONTEXT('USERENV', 'CLIENT_INFO'),
    SYS_CONTEXT('USERENV', 'MODULE'),
    SYS_CONTEXT('USERENV', 'ACTION'),
    SYSTIMESTAMP
  );
  COMMIT;  -- autonomous transaction commit — does not affect caller's transaction
EXCEPTION
  WHEN OTHERS THEN
    -- If error logging itself fails, write to alert log and continue
    -- Never let logging failure suppress the original error
    SYS.DBMS_SYSTEM.KSDWRT(
      2,  -- alert log destination
      'ERROR LOG FAILED in capture_error for module=' || p_module ||
      ' SQLCODE=' || SQLCODE
    );
END capture_error;
/

-- ===================================================
-- Usage pattern: every production exception handler
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.payment.reconcile_batch(
  p_batch_id IN NUMBER,
  p_run_date IN DATE
) IS
  v_processed NUMBER := 0;
  v_failed    NUMBER := 0;
BEGIN
  -- Business logic
  FOR rec IN (
    SELECT * FROM io.thecodeforge.payment.pending_transactions
    WHERE batch_id = p_batch_id
  ) LOOP
    BEGIN
      io.thecodeforge.payment.process_transaction(
        p_txn_id    => rec.transaction_id,
        p_batch_id  => p_batch_id
      );
      v_processed := v_processed + 1;
    EXCEPTION
      WHEN OTHERS THEN
        -- Capture: stack + parameters + context
        io.thecodeforge.logging.capture_error(
          p_module     => 'RECONCILE_BATCH.LOOP',
          p_parameters =>
            'batch_id='     || p_batch_id        || CHR(10) ||
            'run_date='     || TO_CHAR(p_run_date, 'YYYY-MM-DD') || CHR(10) ||
            'txn_id='       || rec.transaction_id || CHR(10) ||
            'processed_so_far=' || v_processed
        );
        v_failed := v_failed + 1;
        -- For batch processing: log-and-continue pattern
        -- Do NOT re-raise here — allows batch to process remaining records
    END;
  END LOOP;
  
  -- Report completion with counts
  io.thecodeforge.logging.capture_error(
    p_module     => 'RECONCILE_BATCH.SUMMARY',
    p_parameters =>
      'batch_id='  || p_batch_id  || CHR(10) ||
      'processed=' || v_processed || CHR(10) ||
      'failed='    || v_failed
  );
  
  COMMIT;
END reconcile_batch;
/

-- ===================================================
-- Post-mortem query: find the failing call chain
-- Run this after a production error to reconstruct the stack
-- ===================================================
SELECT
  el.error_id,
  el.module_name,
  el.error_code,
  el.error_message,
  el.parameters,
  el.call_stack,
  el.error_backtrace,
  el.db_user,
  el.os_user,
  el.created_at
FROM io.thecodeforge.logging.error_log el
WHERE el.created_at > SYSTIMESTAMP - INTERVAL '1' HOUR
  AND el.error_code != 0   -- exclude informational entries
ORDER BY el.created_at DESC
FETCH FIRST 20 ROWS ONLY;