Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Q:SQL*Loader Problem with Date ORA-01861
Ralf,
Note 1074884.6 on Metalink suggests to use a to_date function. They use this example:
Example controlfile if the NLS_DATE_FORMAT='dd-mon-yy':
load data
infile *
insert into table loaddate
fields terminated by "," optionally enclosed by '"'
(col1,
col2 integer external,
col3 date 'dd-mon-yy' "to_date(:col3,'yyyy-mon-dd:hh24:mi:ss')" )
begindata
"rjohnson",1234,"2000-jun-23:01:00:00"
hth
Peter
"Ralf Bender" <ralf.bender_at_arcor.de> schreef in bericht news:20021031.14031230.3551083122_at_mis.configured.host... Hi Daniel, Hi Niall,
it's very frustrating!!!
I changed NLS_DATE_FORMAT to YYYY-MM-DD in init.ora. Error Occurs. I'm
cutting off the first dates from the whole file, because Niall makes me
nervous, BUT NOTHING. The error is always the same.
I absolutly don't know what to do now.
Is there someone who can help, PLEASE
ralf
>>>>>>>>>>>>>>>>>> Ursprüngliche Nachricht <<<<<<<<<<<<<<<<<<
Am 31.10.2002, 09:22:41, schrieb "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> zum Thema Re: Q:SQL*Loader Problem with Date ORA-01861:
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3DC04164.B42B2B49_at_exesolutions.com...
> > Ralf Bender wrote:
> >
> > > Hi Daniel,
> > > Well, I changed my Controlfile:
> > > LOAD DATA
> > > INFILE 'R8'
> > > into table R8
> > > fields terminated by "," optionally enclosed by '"'
> > > (
> > > notesdocid,
> > > gpfearcid,
> > > arclevel,
> > > decdocid INTEGER EXTERNAL TERMINATED BY WHITESPACE,
> > > doctype INTEGER EXTERNAL TERMINATED BY WHITESPACE,
> > > ablagekreis,
> > > mandant,
> > > rechnr,
> > > dokdatum DATE "YYYY-MM-DD",
> > > kundennr
> > > )
> > >
> > > ...BUT, the same Error occurs! May the quotes be a problem?
> Are you absolutely sure that all of your dates in the file are of the
form
> YYYY-MM-DD?
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
Received on Thu Oct 31 2002 - 08:45:35 CST