Re: Strange timestamp problem, ORA-01861

From: Frode Langseth <frode.langseth_at_sensewave.com>
Date: 8 Aug 2003 03:55:19 -0700
Message-ID: <4fd352f8.0308080255.49bad845@posting.google.com>

Ed, you put me on the right track ....

When I modified the control file, from
RS_CREATIONTIME TIMESTAMP,
to
RS_CREATIONTIME TIMESTAMP 'YYYY-MM-DD-HH24.MI.SS.FF', things started to behave better ...

Before the change in the control file, it seemed like the loader used the NLS_TIMESTAMP_FORMAT variable, but the variable's format was wrong.
The log output changed from

RS_CREATIONTIME                      NEXT     * O(X07) O(') DATETIME

"YYYY-MM-DD-HH24.MI.SS.FF"
to

RS_CREATIONTIME                      NEXT     * O(X07) O(') DATETIME

YYYY-MM-DD-HH24.MI.SS.FF and the loading of dates now seems to work!!

Thanks!

Best regards

Frode Langseth

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0308071201.67c59e65_at_posting.google.com>...
> frode.langseth_at_sensewave.com (Frode Langseth) wrote in message news:<4fd352f8.0308070553.144b1b78_at_posting.google.com>...
> > Hi!
> >
> > I'm trying to import some data into a Oracle 9.2.0.3.0 database using
> > SQLLoader, and get some trouble loading into one table.
> >
> > This is the the table creation statement:
> > CREATE TABLE RS_RESOURCE(
> > RS_PK NUMBER (11,0) NOT NULL,
> > RS_CM_PATH VARCHAR2 (255) NOT NULL,
> > RS_MT_PK NUMBER (11,0) ,
> > RS_RTM_PK NUMBER (11,0) ,
> > RS_CREATIONTIME TIMESTAMP ,
> > RS_MODIFIEDTIME TIMESTAMP ,
> []
> > RS_DELETED NUMBER (6,0) );
> >
> > I set the NLS_TIMESTAMP_FORMAT to "YYYY-MM-DD-HH24.MI.SS.FF", the
> > timestamp strings look like this: '2001-09-19-15.24.00.000000'
> >
> > When I run SQLLoader, I get this message in the log file:
> > "Record 1: Rejected - Error on table RS_RESOURCE, column
> > RS_CREATIONTIME.
> > ORA-01861: literal does not match format string"
> >
> []
> >
> > I really doesn't understand why I get this error message from
> > SQLLoader, so any help would be great appriciated!!
> >
> >
> > Best regards,
> >
> > Frode Langseth
>
> Maybe the SQL*Loader is not using the NLS_TIMESTAMP_FORMAT ?
>
> try putting the format in the control file (see manual for details)
>
> ed
Received on Fri Aug 08 2003 - 05:55:19 CDT