Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sqlldr returns ORA-00984 - trouble with date format Access -> 8i

Ian Vaughan
HPE Blogger

sqlldr returns ORA-00984 - trouble with date format Access -> 8i

Howdy,
I am trying to load a csv text file that was exported from MS Access 97 into Oracle. My sqlldr log file is full of the following:
"Record 1: Rejected - Error on table SCOTT.JOBSHEET, column DATE_LIVE.
ORA-00984: column not allowed here".
I have got as far as discovering that Oracle doesn't like unquoted date strings - my question is, how do I do a conversion of a ,mm/dd/yy 0:00:00, field in my text file so that it works with a field of type DATE in oracle?
As ever, points for answers..
Thanks
Ian
Hope that helps - please click "Thumbs up" for Kudos if it does
## ---------------------------------------------------------------------------##
Which is the only cheese that is made backwards?
Edam!
Tweets: @2techie4me
1 REPLY
Andreas D. Skjervold
Honored Contributor

Re: sqlldr returns ORA-00984 - trouble with date format Access -> 8i

Hi

Just specify the date format in the SQL*Loader controlfile like this:

LOAD DATA
INFILE '< datafil.dat>'
APPEND --(el REPLACE, INSERT er default;krever tom tab)
INTO TABLE
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '???'
(col_name1, col_name2, col_name3...
date_col DATE ???mm/dd/yy 0:00:00???)

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!