Oracle 12c - SQL Loader Invalid month Error

Question

I'm getting ORA-01843: not a valid month error while loading the data using tab delimited text file with sql loader 11.1.0.6.0 on oracle 12c.

control file:

options (skip=1)
load data
truncate
into table test_table
fields terminated by '\t' TRAILING NULLCOLS
( 
  type,
  rvw_date "case when :rvw_date = 'NULL' then null  WHEN       REGEXP_LIKE(:rvw_date, '\d{4}/\d{2}/\d{2}') THEN to_date(:rvw_date,'yyyy/mm/dd')   else to_date(:rvw_date,'mm-dd-yy') end"
)

Data:

type     rvw_date
Phone    2014/01/29
Phone    2014/02/13
Field    NULL
Phone    01/26/15
Field    02/25/12

Schema:

create table test_table
(
    type varchar2(20),
    rvw_date date
)

Why have you specified mm-dd-yyyy when you're looking for yyyy/mm/dd? — Alex Poole
i was testing with yyyy/mm/dd itself but later I changed to mm-dd-yyyy to see whether it works. I have edited the question. Thnx — Koushik Ravulapelli

Alex Poole Alex Poole · Accepted Answer · 2016-12-30T16:13:47

The SQL*Loader control file is interpreting a backslash as an escape character, it seems. The SQL operator section shows double-quotes being escaped too; it ins't obvious it would apply to anything else, but it kind of makes sense that a single backslash would always be assumed to be escaping something.

Your regular expression pattern needs to double-escape the \d to make the pattern work in the file as it does in plain SQL. At the moment the pattern is not matched, so all the values to go the else, which is the wrng format mask (even when they are both corrected).

This works:

options (skip=1)
load data
truncate
into table test_table
fields terminated by '\t' TRAILING NULLCOLS
(
  type,
  rvw_date "case when :rvw_date = 'NULL' then null when REGEXP_LIKE(:rvw_date,'\\d{4}/\\d{2}/\\d{2}') then to_date(:rvw_date,'yyyy/mm/dd') else to_date(:rvw_date,'mm/dd/rr') end"
)

With your original data that creates rows:

alter session set nls_date_format = 'YYYY-MM-DD';
select * from test_table;

TYPE                 RVW_DATE  
-------------------- ----------
Phone                2014-01-29
Phone                2014-02-13
Field                          
Phone                2015-01-26
Field                2012-02-25

Oracle 12c - SQL Loader Invalid month Error

1 Answers