Oracle Database Error:
ORA-01843

What does this error mean?

Oracle cannot parse the month component of a date string because it does not match the expected format or language setting.

Common causes

• 1Source date uses abbreviated English month names (Jan, Feb) but Oracle session uses a different NLS_DATE_LANGUAGE
• 2Date format mask uses MON (month abbreviation) but the string uses MONTH (full name) or a numeric month
• 3Date string uses locale-specific month names that don't match Oracle's language setting
• 4ADF sends dates in MM/DD/YYYY format when Oracle expects DD/MM/YYYY (NLS_DATE_FORMAT mismatch)

How to fix it

1. 1Step 1: Specify the NLS_DATE_LANGUAGE explicitly: `TO_DATE(col, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')`.
2. 2Step 2: Use numeric month format to avoid language dependency: `TO_DATE(col, 'YYYY-MM-DD')` instead of `TO_DATE(col, 'DD-MON-YYYY')`.
3. 3Step 3: Check and set the pipeline session's NLS_DATE_FORMAT: `ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';`.
4. 4Step 4: Pre-process dates in ADF to convert to ISO 8601 format (YYYY-MM-DD) before sending to Oracle.
5. 5Step 5: Validate date strings in the source before the Oracle insert step.

Example log output

ORA-01843: not a valid month
SQL: INSERT INTO events (event_date) VALUES (TO_DATE('15-Jan-2024', 'DD-MON-YYYY'))
-- Session NLS_DATE_LANGUAGE was DUTCH, not AMERICAN

Frequently asked questions

Other data errors