Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Q:SQL*Loader Problem with Date ORA-01861

Re: Q:SQL*Loader Problem with Date ORA-01861

From: Peter van Rijn <p.vanrijnREMOVE_at_THISzhew.nl>
Date: Thu, 31 Oct 2002 15:45:35 +0100
Message-ID: <us2glnndueu81b@corp.supernews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US