SQL Loader Date Format

[dnunknown]

Hi Tim,

11.2

I am loading some data from a file and I am having issue w/ the date format

In the DB the column is a date type
In the text file the data is like this for the date column;
1999-07-30 18:41:58
So I have a line in my control file for the sql load that is

Code: Select all

MODIFYDATE  DATE "YYYY-MM-DD HH24:MI:SS" ,
Error -
Record 1: Rejected - Error on table <owner>.<table>, column MODIFYDATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

So then I change it to

Code: Select all

MODIFYDATE  EXPRESSION "TO_DATE(MODIFYDATE, 'YYYY-MM-DD HH24:MI:SS')" ,
Error -
SQL*Loader-350: Syntax error at line 34.
Expecting keyword INTO, found "1999".
1999-07-30 18:41:58

Have also looked on your site and used example code from ;
http://www.oracle-base.com/articles/10g/load-lob-data-using-sql-loader.php
To make it this;

Code: Select all

MODIFYDATE DATE "YYYY-MM-DD HH24:MI:SS" ":MODIFYDATE",
Error -
Record 1: Rejected - Error on table AGILEPROD.ITEM_2, column MODIFYDATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


The complete control file (.dat) looks like this w/ the column for MODIFYDATE being altered for the above senerios;

Code: Select all

load data
INFILE '<file>' "str '\n'"
INTO TABLE <owner>.<table>
APPEND
FIELDS TERMINATED BY '|^'  optionally enclosed by '"' trailing nullcols
(col_1 INTEGER,
MODIFYDATE DATE "YYYY-MM-DD HH24:MI:SS" ":MODIFYDATE",
col_3 INTEGER
)

How can I resolve this format error?

Thanks,
Steve

[Tim...]

Hi.

The date formatting looks fine to me. I would suggest that it is the data you are trying to load that is the problem.

Try it against a single row with correct data in each field and see if that works before trying to load a whole file.

Cheers

Tim...

[dnunknown]

I created my own file and it loaded w/o issue - thanks

[Tim...]