Oracle Error ORA-01858: A Non-Numeric Character Was Found

-- ============================================================
-- CORRECT EXAMPLES — format mask matches input structure
-- ============================================================

-- ISO 8601 — always the safest choice for cross-system exchange
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') FROM dual;
-- Result: 15-MAR-24 (displayed per session NLS_DATE_FORMAT)

-- US format with 2-digit month
SELECT TO_DATE('03/15/2024', 'MM/DD/YYYY') FROM dual;

-- European format with 2-digit year (RR = round-trip year: 00-49 = 2000-2049)
SELECT TO_DATE('15-MAR-24', 'DD-MON-RR') FROM dual;

-- ISO 8601 with timestamp
SELECT TO_TIMESTAMP('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Compact numeric format
SELECT TO_DATE('20240315', 'YYYYMMDD') FROM dual;

-- ============================================================
-- FAILING EXAMPLES — each raises ORA-01858, with explanation
-- ============================================================

-- FAILS: 'March' is a word; MM expects two digits
SELECT TO_DATE('2024-March-15', 'YYYY-MM-DD') FROM dual;
-- Oracle reads 'M' at position 6, expects '0'-'9' for MM — ORA-01858

-- FAILS: separator mismatch — input has '/', mask has '-'
SELECT TO_DATE('03/15/2024', 'MM-DD-YYYY') FROM dual;
-- Oracle reads '/' at position 3, expects '-' — ORA-01858

-- FAILS: 'th' ordinal suffix — no format element for it
SELECT TO_DATE('15th-MAR-2024', 'DD-MON-YYYY') FROM dual;
-- Oracle reads 't' after '15', expects '-' — ORA-01858

-- FAILS: '/' where YYYY-MM-DD expects '-'
SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD') FROM dual;
-- Oracle reads '/' at position 5, expects '-' — ORA-01858

-- ============================================================
-- FX MODIFIER — enforces exact matching
-- ============================================================

-- Without FX: succeeds despite missing leading zero
SELECT TO_DATE('3/15/2024', 'MM/DD/YYYY') FROM dual;  -- works

-- With FX: fails — month must be exactly 2 digits
SELECT TO_DATE('3/15/2024', 'FXMM/DD/YYYY') FROM dual;  -- ORA-01858

-- With FX: succeeds — exact match
SELECT TO_DATE('03/15/2024', 'FXMM/DD/YYYY') FROM dual;  -- works

-- ============================================================
-- ANSI DATE LITERAL — no format mask, no NLS dependency
-- ============================================================
-- The ANSI DATE literal always uses YYYY-MM-DD regardless of NLS
SELECT DATE '2024-03-15' FROM dual;
-- Identical behavior in every environment

-- In WHERE clauses — index-safe, NLS-independent
SELECT COUNT(*) FROM orders WHERE order_date >= DATE '2024-01-01';

-- ============================================================
-- DIAGNOSTIC: Check exact position of the mismatch
-- ============================================================
-- When ORA-01858 fires on a specific input, find the mismatch position
SELECT
  LENGTH('03/15/2024')                AS input_length,
  DUMP('03/15/2024', 10)              AS input_bytes,
  LENGTH('MM-DD-YYYY')                AS mask_length,
  DUMP('MM-DD-YYYY', 10)              AS mask_bytes
FROM dual;
-- Compare position 3: input has 47 (ASCII for '/'), mask expects 45 (ASCII for '-')

-- ============================================================
-- IMPLICIT CONVERSION — the invisible ORA-01858 time bomb
-- ============================================================

-- This query's success depends entirely on NLS_DATE_FORMAT
SELECT * FROM orders WHERE order_date = '15-MAR-2024';
-- Works when NLS_DATE_FORMAT = 'DD-MON-YYYY'
-- Raises ORA-01858 when NLS_DATE_FORMAT = 'MM/DD/YYYY'
-- Raises ORA-01858 when NLS_DATE_FORMAT = 'YYYY-MM-DD'

-- ============================================================
-- DIAGNOSTIC COMMANDS — identify the active NLS settings
-- ============================================================

-- Session-level settings (what YOUR query uses)
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN (
  'NLS_DATE_FORMAT',
  'NLS_LANGUAGE',
  'NLS_TERRITORY',
  'NLS_TIMESTAMP_FORMAT',
  'NLS_TIMESTAMP_TZ_FORMAT'
)
ORDER BY parameter;

-- Database-level defaults (inherited by sessions without explicit override)
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN (
  'NLS_DATE_FORMAT',
  'NLS_LANGUAGE',
  'NLS_TERRITORY'
)
ORDER BY parameter;

-- Instance-level settings (from init.ora / spfile)
SELECT name, value
FROM v$parameter
WHERE name IN ('nls_date_format', 'nls_language', 'nls_territory')
ORDER BY name;

-- ============================================================
-- THE PERFORMANCE IMPACT — implicit conversion prevents index use
-- ============================================================

-- BAD: implicit conversion — Oracle converts every row, full table scan likely
EXPLAIN PLAN FOR
SELECT * FROM orders WHERE order_date = '15-MAR-2024';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for: TABLE ACCESS FULL — the index on order_date is not used

-- GOOD: explicit TO_DATE — optimizer can use the index
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date = TO_DATE('15-MAR-2024', 'DD-MON-YYYY');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for: INDEX RANGE SCAN — full index utilization

-- BEST: ANSI DATE literal — NLS-independent and index-safe
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date = DATE '2024-03-15';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- INDEX RANGE SCAN — same as explicit TO_DATE, simpler syntax

-- ============================================================
-- SAFE DATE PATTERNS IN SQL AND PL/SQL
-- ============================================================

-- In WHERE clauses — use ANSI DATE literal for date-only comparisons
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

-- In WHERE clauses — use explicit TO_DATE for date+time precision
SELECT * FROM orders
WHERE order_date >= TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- In PL/SQL — explicit TO_DATE in procedures
DECLARE
  v_cutoff DATE := TO_DATE('2024-03-15', 'YYYY-MM-DD');
BEGIN
  UPDATE orders
  SET status = 'ARCHIVED'
  WHERE order_date < v_cutoff;
END;
/

-- From JDBC — pass DATE type directly, no conversion needed
-- Java: preparedStatement.setDate(1, java.sql.Date.valueOf("2024-03-15"));
-- This completely bypasses the NLS_DATE_FORMAT question

-- ============================================================
-- SETTING NLS EXPLICITLY — for sessions processing external data
-- ============================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.initialize_session IS
BEGIN
  -- Set NLS context explicitly so the procedure behaves the same
  -- regardless of how the session was initialized
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF6''';
END initialize_session;
/

-- ============================================================
-- MONTH ABBREVIATIONS BY NLS_LANGUAGE
-- ============================================================
-- AMERICAN: JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-- GERMAN:   JAN FEB MRZ APR MAI JUN JUL AUG SEP OKT NOV DEZ
-- FRENCH:   JANV FEVR MARS AVRI MAI JUIN JUIL AOUT SEPT OCT NOV DEC
-- SPANISH:  ENE FEB MAR ABR MAY JUN JUL AGO SEP OCT NOV DIC
-- PORTUGUESE: JAN FEV MAR ABR MAI JUN JUL AGO SET OUT NOV DEZ

-- This succeeds only in AMERICAN or compatible NLS_LANGUAGE:
SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY') FROM dual;
-- In FRENCH session: ORA-01858 because 'JAN' ≠ 'JANV'
-- In GERMAN session: ORA-01858 because position-3 letters differ for some months

-- ============================================================
-- SAFE APPROACH 1: Numeric format — language-independent
-- ============================================================
-- MM is always two decimal digits, regardless of language
SELECT TO_DATE('15-01-2024', 'DD-MM-YYYY') FROM dual;  -- Works everywhere
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;  -- Works everywhere

-- ============================================================
-- SAFE APPROACH 2: Set NLS_LANGUAGE explicitly when processing
-- localized month names
-- ============================================================
BEGIN
  -- Set AMERICAN for the duration of this procedure's execution
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN''';

  -- Now MON abbreviations are predictably English
  SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY') INTO v_date FROM dual;
END;
/

-- ============================================================
-- SAFE APPROACH 3: Use the NLS argument in TO_DATE_WITH_LANG
-- (Oracle 12c+: TO_DATE with NLS_DATE_LANGUAGE parameter)
-- ============================================================
SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY',
  'NLS_DATE_LANGUAGE = AMERICAN') FROM dual;
-- This form overrides NLS_LANGUAGE for this single call
-- without altering the session setting

SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY',
  'NLS_DATE_LANGUAGE = FRENCH') FROM dual;
-- This would expect 'JANV' for January in French

-- ============================================================
-- io.thecodeforge.util SAFE CONVERSION FUNCTIONS
-- ============================================================

-- safe_to_date: returns p_default instead of raising on failure
CREATE OR REPLACE FUNCTION io.thecodeforge.util.safe_to_date(
  p_input   IN VARCHAR2,
  p_format  IN VARCHAR2,
  p_default IN DATE DEFAULT NULL
) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TO_DATE(TRIM(p_input), p_format);
EXCEPTION
  WHEN OTHERS THEN
    RETURN p_default;
END safe_to_date;
/

-- safe_to_date_american: always parses with AMERICAN month names
CREATE OR REPLACE FUNCTION io.thecodeforge.util.safe_to_date_american(
  p_input   IN VARCHAR2,
  p_format  IN VARCHAR2,
  p_default IN DATE DEFAULT NULL
) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TO_DATE(
    TRIM(p_input),
    p_format,
    'NLS_DATE_LANGUAGE = AMERICAN'
  );
EXCEPTION
  WHEN OTHERS THEN
    RETURN p_default;
END safe_to_date_american;
/

-- Usage examples
SELECT io.thecodeforge.util.safe_to_date('15-JAN-2024', 'DD-MON-YYYY', SYSDATE)
  FROM dual;
-- Returns parsed date in any session, falls back to SYSDATE on failure

SELECT io.thecodeforge.util.safe_to_date_american('15-JAN-2024', 'DD-MON-YYYY', NULL)
  FROM dual;
-- Parses 'JAN' as January regardless of session NLS_LANGUAGE

-- ============================================================
-- DIAGNOSTIC: check what month names your session expects
-- ============================================================
SELECT
  TO_CHAR(TO_DATE('2024-' || LPAD(m,2,'0') || '-01', 'YYYY-MM-DD'), 'MON') AS abbrev,
  TO_CHAR(TO_DATE('2024-' || LPAD(m,2,'0') || '-01', 'YYYY-MM-DD'), 'MONTH') AS full_name,
  m AS month_number
FROM (
  SELECT LEVEL AS m FROM dual CONNECT BY LEVEL <= 12
)
ORDER BY m;
-- Shows the month abbreviations and names Oracle expects for this session's NLS_LANGUAGE

-- ============================================================
-- FLEXIBLE MULTI-FORMAT DATE PARSER
-- ============================================================
CREATE OR REPLACE FUNCTION io.thecodeforge.util.flexible_to_date(
  p_input IN VARCHAR2
) RETURN DATE DETERMINISTIC IS
  v_trimmed VARCHAR2(100) := TRIM(p_input);
  v_result  DATE;

  TYPE t_format_tab IS TABLE OF VARCHAR2(40);
  v_formats t_format_tab := t_format_tab(
    'YYYY-MM-DD',            -- ISO 8601 — most common in modern APIs
    'YYYY/MM/DD',            -- ISO with slash separators
    'YYYYMMDD',              -- Compact ISO — common in EDI and legacy
    'DD-MON-YYYY',           -- Oracle default display format
    'DD-MON-YY',             -- Oracle 2-digit year
    'MM/DD/YYYY',            -- US format
    'DD/MM/YYYY',            -- European format
    'MM-DD-YYYY',            -- US with dash separators
    'DD.MM.YYYY',            -- Central European format (dots)
    'YYYY-MM-DD HH24:MI:SS', -- ISO with time
    'DD-MON-YYYY HH24:MI:SS',
    'MM/DD/YYYY HH24:MI:SS',
    'YYYY-MM-DD"T"HH24:MI:SS' -- ISO 8601 with T separator (JSON/XML)
  );
BEGIN
  -- Reject obviously empty input
  IF v_trimmed IS NULL OR LENGTH(v_trimmed) = 0 THEN
    RETURN NULL;
  END IF;

  -- Try each format in order — first success wins
  FOR i IN 1 .. v_formats.COUNT LOOP
    BEGIN
      v_result := TO_DATE(v_trimmed, v_formats(i),
        'NLS_DATE_LANGUAGE = AMERICAN');  -- Always use AMERICAN for MON elements
      RETURN v_result;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;  -- Try next format
    END;
  END LOOP;

  -- All formats failed — raise descriptive error with raw value
  RAISE_APPLICATION_ERROR(
    -20001,
    'Cannot parse date: [' || v_trimmed || '] — tried ' ||
    v_formats.COUNT || ' format masks. Use YYYY-MM-DD for unambiguous parsing.'
  );
END flexible_to_date;
/

-- ============================================================
-- BATCH PROCESSING WITH PER-RECORD ERROR HANDLING
-- ============================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.load_transactions IS
  v_processed    PLS_INTEGER := 0;
  v_errors       PLS_INTEGER := 0;
  v_batch_start  TIMESTAMP := SYSTIMESTAMP;
  v_parsed_date  DATE;
BEGIN
  -- Set NLS context explicitly — never inherit from connection pool
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN''';

  FOR rec IN (
    SELECT txn_id, date_string, amount, source_region
    FROM io.thecodeforge.staging.transactions
    WHERE load_status IS NULL
    ORDER BY txn_id
  ) LOOP
    BEGIN
      -- Attempt flexible date parsing
      v_parsed_date := io.thecodeforge.util.flexible_to_date(rec.date_string);

      -- Insert the successfully parsed record
      INSERT INTO io.thecodeforge.prod.transactions (
        txn_id, txn_date, amount, source_region, loaded_at
      ) VALUES (
        rec.txn_id, v_parsed_date, rec.amount,
        rec.source_region, SYSTIMESTAMP
      );

      -- Mark the staging record as successfully loaded
      UPDATE io.thecodeforge.staging.transactions
      SET load_status = 'LOADED', loaded_at = SYSTIMESTAMP
      WHERE txn_id = rec.txn_id;

      v_processed := v_processed + 1;

      -- Commit in batches to limit undo usage
      IF MOD(v_processed, 1000) = 0 THEN
        COMMIT;
        io.thecodeforge.logging.logger_pkg.info(
          'etl.load_transactions',
          'Checkpoint: processed=' || v_processed || ' errors=' || v_errors
        );
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        v_errors := v_errors + 1;

        -- Log the failure with full diagnostic context
        -- The raw_value column is what makes this log actionable
        INSERT INTO io.thecodeforge.etl.error_log (
          batch_name, record_id, error_code, error_message,
          raw_value, source_region, created_at
        ) VALUES (
          'TXN_LOAD', rec.txn_id, SQLCODE, SUBSTR(SQLERRM, 1, 4000),
          rec.date_string, rec.source_region, SYSTIMESTAMP
        );

        -- Mark staging record as failed
        UPDATE io.thecodeforge.staging.transactions
        SET load_status = 'ERROR', error_message = SUBSTR(SQLERRM, 1, 2000)
        WHERE txn_id = rec.txn_id;

        -- Continue to the next record — do NOT raise or re-raise here
    END;
  END LOOP;

  -- Final commit
  COMMIT;

  -- Summary log
  io.thecodeforge.logging.logger_pkg.info(
    'etl.load_transactions',
    'Complete: processed=' || v_processed ||
    ' errors=' || v_errors ||
    ' duration=' || ROUND(
      (CAST(SYSTIMESTAMP AS DATE) - CAST(v_batch_start AS DATE)) * 86400, 1
    ) || 's'
  );

  -- Raise summary error if error rate exceeds 5%
  IF v_errors > 0 AND v_processed + v_errors > 0 THEN
    IF (v_errors / (v_processed + v_errors)) > 0.05 THEN
      RAISE_APPLICATION_ERROR(
        -20002,
        'TXN_LOAD error rate exceeded 5%: ' || v_errors || ' errors from ' ||
        (v_processed + v_errors) || ' records. Check error_log for details.'
      );
    END IF;
  END IF;

END load_transactions;
/

-- ============================================================
-- QUERY THE ERROR LOG AFTER A FAILED BATCH
-- ============================================================
SELECT
  record_id,
  raw_value,
  source_region,
  error_message,
  created_at
FROM io.thecodeforge.etl.error_log
WHERE batch_name = 'TXN_LOAD'
  AND created_at > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY created_at;
-- The raw_value column shows you exactly what failed
-- and what format the source system is sending