SQL Loader returning ORA-01816 failure on date

Question

I'm in the process of converting a system from MySQL to Oracle. Currently hitting a problem of loading the data from csv into the import table. I did find a work-around, but it's not ideal. I'm able to turn all columns into varchar and load. Then do another load from this table to my actual table. But seems this should be possible without the added step. I've never had such a frustrating time trying to get data into a database before. I appreciate any assistance you can offer.

This is failing with ORA-01816, date can't be specified twice

Sample of the csv file

agg_id~agg_name~createdby~createdate~modifiedby~modifieddate
5~mae_west~mars~"21-OCT-04 01.03.23.966000 PM"~tars~"16-SEP-16 03.11.22.256000 PM"
18~37arrows~azdven~"27-AUG-10 12.10.12.214000 PM"~tars~"16-SEP-16 04.16.01.171000 PM"

This is the SQL Loader ctl file

OPTIONS ( SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE AGGREGATOR
FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
 FILE_ROW_ID RECNUM,
 EXTRACT_DATE SYSDATE,
 AGG_ID,
 AGG_NAME,
 CREATED_BY,
 CREATED_DATE DATE 'DD-Month-YY HH.MI.SS AM',
 MODIFIED_BY,
 MODIFIED_DATE DATE 'DD-Month-YY HH.MI.SS AM')

The date fields need to be specific as following

EXTRACT_DATE '09/19/2016' 'MM/DD/YYYY'
CREATED_DATE '10/21/2004 01:03:24 PM' 'MM/DD/YYYY HH:MI:SS AM'
MODIFIED_DATE '9/16/2016 03:11:22 PM' 'MM/DD/YYYY HH:MI:SS AM'

NLS_DATE is set as 'MM-RR-YYYY'

CREATE TABLE AGGREGATOR
(
  FILE_ROW_ID        NUMBER(12)                 NOT NULL,
  EXTRACT_DATE       DATE                       NOT NULL,
  AGG_ID             NUMBER(12)                 NOT NULL,
  AGG_NAME           VARCHAR2(200 BYTE)         NOT NULL,
  CREATED_BY         VARCHAR2(35 BYTE)          NOT NULL,
  CREATED_DATE       DATE                       NOT NULL,
  MODIFIED_BY        VARCHAR2(35 BYTE)          NOT NULL,
  MODIFIED_DATE      DATE                       NOT NULL
)

Why are you using DATE conversion when you already have varchar2 datatype for the date fields? Why not insert them as it is? Secondly, why you are using varchar2 datatype to store a date? — San
I inadvertently grabbed my work-around table which replaced DATE datatype with varchar2 datatype. I have replaced it with the original table definition. My intent is to store this information in a DATE field. However, I'm having zero success reading this file into the format. I need to add an additional layer, if I go with the work-around, which is not ideal. — Allan L
It appears that the date column itself is the problem. Oracle is unable to convert this value to a meaningful DATE. 21-OCT-04 01.03.23.966000 PM Appears I will need a custom function to disassemble this string into the date components. — Allan L
After further analysis, it appears the milliseconds are the source of the failure. If I remove the .966000 from the string, the TO_DATE will operate as expected. String: 21-OCT-04 01.03.23.966000 PM — Allan L

Allan L Allan L · Accepted Answer · 2016-09-20T17:58:57

This issue has been resolved. Changing the DATE to TIMESTAMP in the final two date column, allowed the file to be read without issue. I was unaware that the field was a timestamp, expecting it to format as a date.

Updated File Load CTL:

OPTIONS ( SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE AGGREGATOR
FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
 FILE_ROW_ID RECNUM,
 EXTRACT_DATE SYSDATE,
 AGG_ID,
 AGG_NAME,
 CREATED_BY,
 CREATED_DATE TIMESTAMP,
 MODIFIED_BY,
 MODIFIED_DATE TIMESTAMP)

Updated Table Definition:

CREATE TABLE AGGREGATOR
(
  FILE_ROW_ID        NUMBER(12)                 NOT NULL,
  EXTRACT_DATE       DATE                       NOT NULL,
  AGG_ID             NUMBER(12)                 NOT NULL,
  AGG_NAME           VARCHAR2(200 BYTE)         NOT NULL,
  CREATED_BY         VARCHAR2(35 BYTE)          NOT NULL,
  CREATED_DATE       TIMESTAMP                  NOT NULL,
  MODIFIED_BY        VARCHAR2(35 BYTE)          NOT NULL,
  MODIFIED_DATE      TIMESTAMP                  NOT NULL
)

SQL Loader returning ORA-01816 failure on date

1 Answers