to_date('OCT','MON') ORA-01843: not a valid month

ORA-01843: not a valid month 

that can mistake with some format with some language.

SQL> select  SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,   SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual;

DF                   DL

-------------------- ---------------

DD-MON-RR            AMERICAN

SQL> select to_date('01/OCT/2009','DD/MON/YYYY') from dual;

TO_DATE('

---------

01-OCT-09

SQL> alter session set NLS_DATE_LANGUAGE=GERMAN;

Session altered.

SQL> select  SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,   SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual;

DF                   DL

-------------------- ---------------

DD-MON-RR            GERMAN

SQL> select to_date('01/OCT/2009','DD/MON/YYYY') from dual;

select to_date('01/OCT/2009','DD/MON/YYYY') from dual

               *

ERROR at line 1:

ORA-01843: not a valid month

SQL> select to_date('01/OKT/2009','DD/MON/YYYY') from dual;

TO_DATE('

---------

01-OKT-09

That, German use "OKT", not "OCT".

Anyway if use another else language... we need to know about that format:

SQL>  alter session set NLS_DATE_LANGUAGE=french;

Session altered.

SQL> select  SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,   SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual;

DF                   DL

-------------------- ---------------

DD-MON-RR            FRENCH

SQL> select to_date('01/OCT/2009','DD/MON/YYYY') from dual;

select to_date('01/OCT/2009','DD/MON/YYYY') from dual

               *

ERROR at line 1:

ORA-01843: not a valid month

SQL> select to_date('01/OKT/2009','DD/MON/YYYY') from dual;

select to_date('01/OKT/2009','DD/MON/YYYY') from dual

               *

ERROR at line 1:

ORA-01843: not a valid month

SQL> select to_date('01/OCT. /2009','DD/MON/YYYY') from dual;

TO_DATE('01

-----------

01-OCT. -09

Or... use nls_date_language to help

SQL> select to_date('01/OCT/2009','DD/MON/YYYY','nls_date_language=american') from dual;

TO_DATE('01

-----------

01-OCT. -09

Anyway before use any query, that should check CBO (use Execution Plan...) before.