ORA-01403 - Missing Config Row Halts 4-Hour Batch

-- ============================================================
-- SELECT INTO: the one-row contract
-- Zero rows = ORA-01403, Multiple rows = ORA-01422
-- Runnable on Oracle 19c, 21c, 23ai
-- ============================================================

-- Setup: create a test table
CREATE TABLE demo_accounts (
  account_id   NUMBER PRIMARY KEY,
  account_type VARCHAR2(30) NOT NULL,
  balance      NUMBER(15,2) NOT NULL,
  status       VARCHAR2(10) DEFAULT 'ACTIVE'
);

INSERT INTO demo_accounts VALUES (1, 'CHECKING', 5000.00, 'ACTIVE');
INSERT INTO demo_accounts VALUES (2, 'SAVINGS', 12000.00, 'ACTIVE');
INSERT INTO demo_accounts VALUES (3, 'CHECKING', 800.00, 'CLOSED');
COMMIT;

-- ============================================================
-- Demo 1: ORA-01403 — zero rows
-- ============================================================

DECLARE
  v_balance NUMBER;
BEGIN
  -- Account 99 does not exist — this raises ORA-01403
  SELECT balance INTO v_balance
  FROM demo_accounts
  WHERE account_id = 99;

  DBMS_OUTPUT.PUT_LINE('Balance: ' || v_balance);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ORA-01403 caught: account 99 does not exist');
END;
/

-- ============================================================
-- Demo 2: ORA-01422 — multiple rows
-- ============================================================

DECLARE
  v_balance NUMBER;
BEGIN
  -- Two accounts have type CHECKING — this raises ORA-01422
  SELECT balance INTO v_balance
  FROM demo_accounts
  WHERE account_type = 'CHECKING';

  DBMS_OUTPUT.PUT_LINE('Balance: ' || v_balance);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('ORA-01422 caught: multiple CHECKING accounts exist');
END;
/

-- ============================================================
-- Demo 3: NULL comparison trap
-- NULL = NULL evaluates to FALSE — SELECT INTO returns zero rows
-- ============================================================

DECLARE
  v_balance NUMBER;
  v_search_type VARCHAR2(30) := NULL;  -- NULL parameter
BEGIN
  SELECT balance INTO v_balance
  FROM demo_accounts
  WHERE account_type = v_search_type;  -- NULL = 'CHECKING' is FALSE
                                        -- NULL = NULL is also FALSE
  DBMS_OUTPUT.PUT_LINE('Balance: ' || v_balance);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ORA-01403 caught: NULL comparison matched zero rows');
    DBMS_OUTPUT.PUT_LINE('Fix: use NVL() or IS NULL in the WHERE clause');
END;
/

-- Cleanup
-- DROP TABLE demo_accounts PURGE;

-- ============================================================
-- CASE 1: Missing data is a BUG — propagate with context
-- Payment config MUST exist for every active account type
-- ============================================================

CREATE OR REPLACE FUNCTION get_payment_config(
  p_account_type IN VARCHAR2
) RETURN payment_config%ROWTYPE IS
  v_config payment_config%ROWTYPE;
BEGIN
  SELECT * INTO v_config
  FROM payment_config
  WHERE account_type = p_account_type
    AND active_flag = 'Y';

  RETURN v_config;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Do NOT return a default — this is a deployment error
    -- The batch should halt and alert the team
    RAISE_APPLICATION_ERROR(
      -20010,
      'Missing payment config for account type: ' || p_account_type
      || ' | This is a deployment error — insert the config row before retrying'
    );
END get_payment_config;
/

-- ============================================================
-- CASE 2: Missing data is a VALID STATE — return a default
-- New users have no preferences yet — this is expected
-- ============================================================

CREATE OR REPLACE FUNCTION get_theme_preference(
  p_user_id IN NUMBER
) RETURN VARCHAR2 IS
  v_theme VARCHAR2(50);
BEGIN
  SELECT theme INTO v_theme
  FROM user_preferences
  WHERE user_id = p_user_id;

  RETURN v_theme;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- New users have no preferences — return system default
    -- No error, no alert — this is normal behavior
    RETURN 'system_default';
END get_theme_preference;
/

-- ============================================================
-- CASE 3: Existence check without SELECT INTO
-- Caller needs to branch on whether data exists
-- Uses the NVL(MAX(...)) pattern — never raises ORA-01403
-- ============================================================

CREATE OR REPLACE FUNCTION get_account_balance_safe(
  p_account_id IN NUMBER
) RETURN NUMBER IS
  v_balance NUMBER;
BEGIN
  -- NVL(MAX(...)) returns NULL wrapped in NVL default when zero rows
  -- MAX on zero rows returns NULL (not ORA-01403)
  -- NVL converts NULL to the default value
  SELECT NVL(MAX(balance), 0)
  INTO v_balance
  FROM demo_accounts
  WHERE account_id = p_account_id;

  RETURN v_balance;
  -- Never raises ORA-01403 — MAX on zero rows returns NULL, NVL returns 0
END get_account_balance_safe;
/

-- ============================================================
-- CASE 4: Existence check using EXISTS subquery
-- Most efficient when you only need a boolean answer
-- ============================================================

CREATE OR REPLACE FUNCTION has_pending_order(
  p_customer_id IN NUMBER
) RETURN BOOLEAN IS
  v_exists NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_exists
  FROM dual
  WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'PENDING'
  );

  RETURN v_exists = 1;
  -- Never raises ORA-01403 — COUNT(*) FROM dual always returns one row
END has_pending_order;
/

-- ============================================================
-- Pattern 1: NVL(MAX(...)) — simplest, 3 lines, no exception
-- Best for: single scalar values where zero rows means default
-- ============================================================

CREATE OR REPLACE FUNCTION get_balance_nvl_max(
  p_account_id IN NUMBER
) RETURN NUMBER IS
  v_balance NUMBER;
BEGIN
  SELECT NVL(MAX(balance), 0)
  INTO v_balance
  FROM demo_accounts
  WHERE account_id = p_account_id;

  RETURN v_balance;
  -- Zero rows: MAX returns NULL, NVL returns 0
  -- One row: MAX returns the value, NVL passes it through
  -- Never raises ORA-01403
END get_balance_nvl_max;
/

-- ============================================================
-- Pattern 2: BULK COLLECT — handles zero and multiple rows
-- Best for: lookups where you need the full row, not just a scalar
-- Note: LIMIT 1 is unnecessary when the WHERE clause uses a
--   primary key — at most one row can match. Use LIMIT 1 when
--   the WHERE clause could match multiple rows and you want only one.
-- ============================================================

CREATE OR REPLACE FUNCTION get_account_bulk(
  p_account_id IN NUMBER
) RETURN demo_accounts%ROWTYPE IS
  TYPE account_tab IS TABLE OF demo_accounts%ROWTYPE;
  v_results account_tab;
  v_empty   demo_accounts%ROWTYPE;  -- All fields NULL
BEGIN
  SELECT *
  BULK COLLECT INTO v_results
  FROM demo_accounts
  WHERE account_id = p_account_id;
  -- Primary key lookup: at most one row, LIMIT not needed

  IF v_results.COUNT = 0 THEN
    RETURN v_empty;  -- Return record with all fields NULL
  END IF;

  RETURN v_results(1);
  -- Never raises ORA-01403 — empty collection instead
END get_account_bulk;
/

-- BULK COLLECT with LIMIT — use when WHERE could match many rows
CREATE OR REPLACE FUNCTION get_first_active_account(
  p_account_type IN VARCHAR2
) RETURN demo_accounts%ROWTYPE IS
  TYPE account_tab IS TABLE OF demo_accounts%ROWTYPE;
  v_results account_tab;
  v_empty   demo_accounts%ROWTYPE;
BEGIN
  SELECT *
  BULK COLLECT INTO v_results
  FROM demo_accounts
  WHERE account_type = p_account_type
    AND status = 'ACTIVE'
  ORDER BY account_id
  FETCH FIRST 1 ROW ONLY;  -- Oracle 12c+ row limiting clause

  IF v_results.COUNT = 0 THEN
    RETURN v_empty;
  END IF;

  RETURN v_results(1);
END get_first_active_account;
/

-- ============================================================
-- Pattern 3: Explicit cursor with FETCH
-- Best for: complex logic per row, or when you may need > 1 row
-- ============================================================

CREATE OR REPLACE FUNCTION get_account_cursor(
  p_account_id IN NUMBER
) RETURN demo_accounts%ROWTYPE IS
  CURSOR c_account IS
    SELECT * FROM demo_accounts
    WHERE account_id = p_account_id;
  v_result demo_accounts%ROWTYPE;
BEGIN
  OPEN c_account;
  FETCH c_account INTO v_result;

  IF c_account%NOTFOUND THEN
    CLOSE c_account;
    -- v_result has all fields NULL — return it or handle as needed
    RETURN v_result;
  END IF;

  CLOSE c_account;
  RETURN v_result;
  -- Never raises ORA-01403 — %NOTFOUND check instead
END get_account_cursor;
/

-- ============================================================
-- Pattern 4: COUNT(*) pre-check
-- Best for: when you need to branch on existence before processing
-- Trade-off: two queries instead of one
-- ============================================================

CREATE OR REPLACE PROCEDURE process_account_with_check(
  p_account_id IN NUMBER
) IS
  v_count   NUMBER;
  v_balance NUMBER;
BEGIN
  -- First query: check existence
  SELECT COUNT(*) INTO v_count
  FROM demo_accounts
  WHERE account_id = p_account_id;

  IF v_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Account ' || p_account_id || ' does not exist');
    RETURN;
  END IF;

  -- Second query: safe to use SELECT INTO — row guaranteed to exist
  -- Note: race condition possible if another session deletes between queries
  SELECT balance INTO v_balance
  FROM demo_accounts
  WHERE account_id = p_account_id;

  DBMS_OUTPUT.PUT_LINE('Balance: ' || v_balance);
END process_account_with_check;
/

-- ============================================================
-- Pattern 5: EXISTS subquery with dual
-- Best for: boolean existence check — no data retrieval needed
-- ============================================================

CREATE OR REPLACE FUNCTION account_exists(
  p_account_id IN NUMBER
) RETURN BOOLEAN IS
  v_exists NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_exists
  FROM dual
  WHERE EXISTS (
    SELECT 1 FROM demo_accounts
    WHERE account_id = p_account_id
  );

  RETURN v_exists = 1;
  -- Never raises ORA-01403
  -- More efficient than COUNT(*) on the base table for large tables
  -- EXISTS stops scanning after the first matching row
END account_exists;
/

-- ============================================================
-- Pre-flight validation: check all reference data before batch start
-- ============================================================

CREATE OR REPLACE PACKAGE preflight_pkg AS

  -- Validate all active account types have payment config
  PROCEDURE validate_payment_config;

  -- Generic: validate all FK values in source exist in target
  PROCEDURE validate_foreign_keys(
    p_source_table  IN VARCHAR2,
    p_source_column IN VARCHAR2,
    p_target_table  IN VARCHAR2,
    p_target_column IN VARCHAR2
  );

END preflight_pkg;
/

CREATE OR REPLACE PACKAGE BODY preflight_pkg AS

  PROCEDURE validate_payment_config IS
    v_missing_types VARCHAR2(4000);
  BEGIN
    -- Find account types that exist in accounts but not in payment_config
    SELECT LISTAGG(DISTINCT a.account_type, ', ')
           WITHIN GROUP (ORDER BY a.account_type)
    INTO v_missing_types
    FROM accounts a
    LEFT JOIN payment_config pc
      ON pc.account_type = a.account_type
      AND pc.active_flag = 'Y'
    WHERE pc.account_type IS NULL
      AND a.status = 'ACTIVE';

    IF v_missing_types IS NOT NULL THEN
      RAISE_APPLICATION_ERROR(
        -20010,
        'Pre-flight failed: missing payment config for account types: '
        || v_missing_types
        || ' | Insert config rows before running the batch.'
      );
    END IF;

    DBMS_OUTPUT.PUT_LINE('Pre-flight: payment config validated — all types present');
  END validate_payment_config;

  PROCEDURE validate_foreign_keys(
    p_source_table  IN VARCHAR2,
    p_source_column IN VARCHAR2,
    p_target_table  IN VARCHAR2,
    p_target_column IN VARCHAR2
  ) IS
    v_sql           VARCHAR2(4000);
    v_missing_count NUMBER;
  BEGIN
    -- Validate table/column names to prevent SQL injection
    -- DBMS_ASSERT.SQL_OBJECT_NAME raises ORA-44002 for invalid names
    v_sql := '
      SELECT COUNT(DISTINCT s.' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_source_column) || ')
      FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_source_table) || ' s
      WHERE NOT EXISTS (
        SELECT 1 FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_target_table) || ' t
        WHERE t.' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_target_column)
        || ' = s.' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_source_column) || '
      )';

    EXECUTE IMMEDIATE v_sql INTO v_missing_count;

    IF v_missing_count > 0 THEN
      RAISE_APPLICATION_ERROR(
        -20011,
        'Pre-flight failed: ' || v_missing_count
        || ' distinct values in ' || p_source_table || '.' || p_source_column
        || ' have no matching row in ' || p_target_table || '.' || p_target_column
      );
    END IF;

    DBMS_OUTPUT.PUT_LINE(
      'Pre-flight: FK validation passed for '
      || p_source_table || '.' || p_source_column
      || ' -> ' || p_target_table || '.' || p_target_column
    );
  END validate_foreign_keys;

END preflight_pkg;
/

-- ============================================================
-- Usage: call at batch startup before any processing
-- ============================================================

BEGIN
  -- Validate all reference data
  preflight_pkg.validate_payment_config;
  preflight_pkg.validate_foreign_keys('ACCOUNTS', 'REGION_CODE', 'REGIONS', 'REGION_CODE');
  preflight_pkg.validate_foreign_keys('ORDERS', 'PRODUCT_ID', 'PRODUCTS', 'PRODUCT_ID');

  -- All validations passed — safe to process
  DBMS_OUTPUT.PUT_LINE('All pre-flight checks passed — starting batch');
  -- payment_batch_pkg.process_nightly;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Pre-flight failed: ' || SQLERRM);
    -- Log and alert — do not start the batch
    RAISE;
END;
/

-- ============================================================
-- Skip-and-continue batch processing pattern
-- Individual record failures are logged; batch continues
-- ============================================================

CREATE OR REPLACE PACKAGE batch_processor_pkg AS

  PROCEDURE process_payment_batch(
    p_batch_date      IN DATE,
    p_processed_count OUT NUMBER,
    p_error_count     OUT NUMBER
  );

END batch_processor_pkg;
/

CREATE OR REPLACE PACKAGE BODY batch_processor_pkg AS

  -- Autonomous transaction logging — survives rollback of the record
  PROCEDURE log_batch_error(
    p_batch_date  IN DATE,
    p_account_id  IN NUMBER,
    p_error_code  IN NUMBER,
    p_error_msg   IN VARCHAR2,
    p_error_stack IN VARCHAR2
  ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO batch_error_log (
      batch_date,
      account_id,
      error_code,
      error_message,
      error_stack,
      created_at
    ) VALUES (
      p_batch_date,
      p_account_id,
      p_error_code,
      p_error_msg,
      p_error_stack,
      SYSTIMESTAMP
    );
    COMMIT;  -- Commits only this autonomous transaction
  END log_batch_error;

  PROCEDURE process_payment_batch(
    p_batch_date      IN DATE,
    p_processed_count OUT NUMBER,
    p_error_count     OUT NUMBER
  ) IS
    CURSOR c_accounts IS
      SELECT account_id, account_type, balance
      FROM accounts
      WHERE status = 'ACTIVE'
      ORDER BY account_id;

    v_config  payment_config%ROWTYPE;
    v_fee     NUMBER;
  BEGIN
    p_processed_count := 0;
    p_error_count     := 0;

    FOR rec IN c_accounts LOOP
      BEGIN
        -- This SELECT INTO may raise ORA-01403 for new account types
        SELECT * INTO v_config
        FROM payment_config
        WHERE account_type = rec.account_type
          AND active_flag = 'Y';

        -- Process the payment using the config
        v_fee := rec.balance * v_config.fee_rate;

        UPDATE payment_transactions
        SET fee_amount = v_fee,
            status     = 'PROCESSED',
            processed_at = SYSTIMESTAMP
        WHERE account_id = rec.account_id
          AND batch_date = p_batch_date;

        p_processed_count := p_processed_count + 1;

        -- Commit per record or per batch of N records
        IF MOD(p_processed_count, 1000) = 0 THEN
          COMMIT;
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          -- Skip this record — log the error and continue
          p_error_count := p_error_count + 1;

          log_batch_error(
            p_batch_date  => p_batch_date,
            p_account_id  => rec.account_id,
            p_error_code  => SQLCODE,
            p_error_msg   => SQLERRM,
            p_error_stack => DBMS_UTILITY.FORMAT_ERROR_STACK
                          || CHR(10)
                          || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
          );

          -- Rollback only the current record's changes
          -- Previous committed batches are preserved
      END;
    END LOOP;

    -- Final commit for remaining records
    COMMIT;

    -- Summary output
    DBMS_OUTPUT.PUT_LINE(
      'Batch complete: ' || p_processed_count || ' processed, '
      || p_error_count || ' errors'
    );

    -- Alert if error rate exceeds threshold
    IF p_error_count > 0 AND p_processed_count > 0 THEN
      IF (p_error_count / (p_processed_count + p_error_count)) > 0.05 THEN
        -- More than 5% failure rate — raise alert
        RAISE_APPLICATION_ERROR(
          -20020,
          'Batch completed with high error rate: '
          || p_error_count || ' errors out of '
          || (p_processed_count + p_error_count) || ' total records ('
          || ROUND(100 * p_error_count / (p_processed_count + p_error_count), 1)
          || '%). Check batch_error_log for details.'
        );
      END IF;
    END IF;

  END process_payment_batch;

END batch_processor_pkg;
/

-- ============================================================
-- Batch error log table
-- ============================================================

CREATE TABLE batch_error_log (
  log_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  batch_date    DATE NOT NULL,
  account_id    NUMBER,
  error_code    NUMBER,
  error_message VARCHAR2(4000),
  error_stack   VARCHAR2(4000),
  created_at    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP
);

CREATE INDEX idx_batch_error_date ON batch_error_log(batch_date DESC);
CREATE INDEX idx_batch_error_account ON batch_error_log(account_id);

-- ============================================================
-- Complete SELECT INTO with both failure modes handled
-- ============================================================

CREATE OR REPLACE FUNCTION get_account_details(
  p_account_id IN NUMBER
) RETURN demo_accounts%ROWTYPE IS
  v_result demo_accounts%ROWTYPE;
BEGIN
  SELECT * INTO v_result
  FROM demo_accounts
  WHERE account_id = p_account_id;

  RETURN v_result;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Zero rows: account does not exist
    RAISE_APPLICATION_ERROR(
      -20002,
      'Account not found: ' || p_account_id
    );

  WHEN TOO_MANY_ROWS THEN
    -- Multiple rows: unique constraint violation or missing constraint
    -- This should never happen if account_id has a unique constraint
    RAISE_APPLICATION_ERROR(
      -20003,
      'Duplicate account_id detected: ' || p_account_id
      || ' | Verify unique constraint on demo_accounts.account_id'
    );
END get_account_details;
/

-- ============================================================
-- Verify unique constraints exist on lookup columns
-- Run this audit query periodically
-- ============================================================

SELECT
  t.table_name,
  t.column_name,
  CASE
    WHEN c.constraint_type IS NOT NULL THEN 'UNIQUE CONSTRAINT EXISTS'
    ELSE 'NO UNIQUE CONSTRAINT — potential TOO_MANY_ROWS risk'
  END AS constraint_status
FROM (
  -- Tables and columns used in SELECT INTO statements
  -- Update this list based on your codebase audit
  SELECT 'DEMO_ACCOUNTS' AS table_name, 'ACCOUNT_ID' AS column_name FROM dual
  UNION ALL
  SELECT 'PAYMENT_CONFIG', 'ACCOUNT_TYPE' FROM dual
  UNION ALL
  SELECT 'USER_PREFERENCES', 'USER_ID' FROM dual
) t
LEFT JOIN (
  SELECT
    acc.table_name,
    acc.column_name,
    con.constraint_type
  FROM all_cons_columns acc
  JOIN all_constraints con
    ON con.constraint_name = acc.constraint_name
    AND con.owner = acc.owner
  WHERE con.constraint_type IN ('P', 'U')  -- Primary key or Unique
) c
  ON c.table_name = t.table_name
  AND c.column_name = t.column_name
ORDER BY constraint_status DESC, t.table_name;

-- ============================================================
-- Test framework for ORA-01403 handlers
-- Each test forces the exception and verifies handler behavior
-- ============================================================

-- Test 1: Default value handler returns correct default
CREATE OR REPLACE PROCEDURE test_theme_default IS
  v_theme VARCHAR2(50);
BEGIN
  -- User -1 does not exist — forces NO_DATA_FOUND
  v_theme := get_theme_preference(-1);

  IF v_theme != 'system_default' THEN
    RAISE_APPLICATION_ERROR(
      -20901,
      'FAIL: expected system_default, got ' || NVL(v_theme, 'NULL')
    );
  END IF;

  DBMS_OUTPUT.PUT_LINE('PASS: get_theme_preference returns default for missing user');
END test_theme_default;
/

-- Test 2: Propagation handler raises correct custom error code
CREATE OR REPLACE PROCEDURE test_missing_config_error IS
BEGIN
  BEGIN
    -- Account type that does not exist — forces NO_DATA_FOUND
    DECLARE
      v_config payment_config%ROWTYPE;
    BEGIN
      v_config := get_payment_config('NONEXISTENT_TYPE_XYZ');
      -- If we get here, the handler did not raise — test fails
      RAISE_APPLICATION_ERROR(-20902, 'FAIL: expected exception but got success');
    END;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -20010 THEN
        -- Correct custom error code from the handler
        DBMS_OUTPUT.PUT_LINE('PASS: get_payment_config raises -20010 for missing config');
      ELSIF SQLCODE = -20902 THEN
        -- The handler did not raise — it returned a value
        DBMS_OUTPUT.PUT_LINE('FAIL: handler did not raise exception');
        RAISE;
      ELSE
        -- Wrong error code
        DBMS_OUTPUT.PUT_LINE('FAIL: expected -20010, got ' || SQLCODE);
        RAISE;
      END IF;
  END;
END test_missing_config_error;
/

-- Test 3: Batch skip-and-continue processes valid records and logs failures
CREATE OR REPLACE PROCEDURE test_batch_skip_continue IS
  v_processed NUMBER;
  v_errors    NUMBER;
BEGIN
  -- Setup: insert test accounts with one missing config
  INSERT INTO accounts (account_id, account_type, balance, status)
  VALUES (90001, 'CHECKING', 1000, 'ACTIVE');
  INSERT INTO accounts (account_id, account_type, balance, status)
  VALUES (90002, 'MISSING_TYPE_XYZ', 2000, 'ACTIVE');  -- No config for this type
  INSERT INTO accounts (account_id, account_type, balance, status)
  VALUES (90003, 'SAVINGS', 3000, 'ACTIVE');
  COMMIT;

  -- Run batch
  batch_processor_pkg.process_payment_batch(
    p_batch_date      => TRUNC(SYSDATE),
    p_processed_count => v_processed,
    p_error_count     => v_errors
  );

  -- Verify: 2 processed, 1 error
  IF v_processed != 2 THEN
    RAISE_APPLICATION_ERROR(-20903, 'FAIL: expected 2 processed, got ' || v_processed);
  END IF;

  IF v_errors != 1 THEN
    RAISE_APPLICATION_ERROR(-20904, 'FAIL: expected 1 error, got ' || v_errors);
  END IF;

  -- Verify error was logged
  DECLARE
    v_log_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_log_count
    FROM batch_error_log
    WHERE account_id = 90002
      AND batch_date = TRUNC(SYSDATE);

    IF v_log_count = 0 THEN
      RAISE_APPLICATION_ERROR(-20905, 'FAIL: error not logged for account 90002');
    END IF;
  END;

  DBMS_OUTPUT.PUT_LINE('PASS: batch skips failing record, processes valid ones, logs error');

  -- Cleanup
  DELETE FROM accounts WHERE account_id BETWEEN 90001 AND 90003;
  DELETE FROM batch_error_log WHERE account_id = 90002;
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Cleanup on failure
    DELETE FROM accounts WHERE account_id BETWEEN 90001 AND 90003;
    DELETE FROM batch_error_log WHERE account_id = 90002;
    COMMIT;
    RAISE;
END test_batch_skip_continue;
/

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

BEGIN
  DBMS_OUTPUT.PUT_LINE('=== ORA-01403 Handler Tests ===');
  DBMS_OUTPUT.PUT_LINE('');
  test_theme_default;
  test_missing_config_error;
  test_batch_skip_continue;
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('=== All tests complete ===');
END;
/

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

try {
    CallableStatement cs = conn.prepareCall(
        "BEGIN ? := get_payment_config(?); END;"
    );
    cs.registerOutParameter(1, Types.OTHER);
    cs.setString(2, "PREMIUM_SAVINGS");
    cs.execute();
} catch (SQLException e) {
    if (e.getErrorCode() == 1403) {
        // ORA-01403: no data found
        // In a REST API: return 404
        logger.warn("Data not found", Map.of(
            "oraCode", e.getErrorCode(),
            "procedure", "get_payment_config",
            "param", "PREMIUM_SAVINGS"
        ));
    } else if (e.getErrorCode() == 20010) {
        // Custom error from RAISE_APPLICATION_ERROR
        // Error code 20010 = missing payment config (our convention)
        logger.error("Configuration error", Map.of(
            "oraCode", e.getErrorCode(),
            "message", e.getMessage()
        ));
    } else {
        // Unexpected Oracle error
        logger.error("Database error", Map.of(
            "oraCode", e.getErrorCode(),
            "message", e.getMessage()
        ));
        throw e;  // Rethrow unexpected errors
    }
}


============================================================
PYTHON / python-oracledb
============================================================

import oracledb

try:
    cursor.callfunc('get_payment_config', oracledb.OBJECT,
                    ['PREMIUM_SAVINGS'])
except oracledb.DatabaseError as e:
    error = e.args[0]
    if error.code == 1403:
        # ORA-01403: no data found
        logger.warning('Data not found',
            extra={'ora_code': error.code,
                   'procedure': 'get_payment_config'})
    elif error.code == 20010:
        # Custom error: missing payment config
        logger.error('Configuration error',
            extra={'ora_code': error.code,
                   'message': error.message})
    else:
        logger.error('Database error',
            extra={'ora_code': error.code,
                   'message': error.message})
        raise


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

const oracledb = require('oracledb');

try {
    const result = await connection.execute(
        'BEGIN :result := get_payment_config(:acct_type); END;',
        { result: { dir: oracledb.BIND_OUT, type: oracledb.STRING },
          acct_type: 'PREMIUM_SAVINGS' }
    );
} catch (err) {
    if (err.errorNum === 1403) {
        // ORA-01403: no data found
        console.warn(JSON.stringify({
            level: 'warn',
            oraCode: err.errorNum,
            procedure: 'get_payment_config'
        }));
    } else if (err.errorNum === 20010) {
        // Custom error: missing payment config
        console.error(JSON.stringify({
            level: 'error',
            oraCode: err.errorNum,
            message: err.message
        }));
    } else {
        console.error(JSON.stringify({
            level: 'error',
            oraCode: err.errorNum,
            message: err.message
        }));
        throw err;
    }
}


============================================================
Key points:
============================================================
1. Error code 1403 = ORA-01403 (NO_DATA_FOUND)
2. Error codes 20000-20999 = RAISE_APPLICATION_ERROR custom errors
3. Use the numeric error code for branching — not string matching
4. Log the Oracle error code as a separate field for filtering
5. Map ORA-01403 to HTTP 404 in REST APIs when appropriate
6. Map custom errors to specific HTTP codes based on your convention