Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Ian Vaughan
Honored Contributor

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!