Strange timestamp problem, ORA-01861

From: Frode Langseth <frode.langseth_at_sensewave.com>
Date: 7 Aug 2003 06:53:59 -0700
Message-ID: <4fd352f8.0308070553.144b1b78@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_CONTENTLENGTH NUMBER (11,0)  ,
  RS_RECEPTIONTIME TIMESTAMP  ,
  RS_GRADATION VARCHAR2 (255)  ,
  RS_BAM_PK NUMBER (11,0)  ,
  RS_IM_PK NUMBER (11,0)  ,
  RS_SM_PK NUMBER (11,0)  ,
  RS_STATUS VARCHAR2 (50)  ,
  RS_STATUSTIME TIMESTAMP  ,
  RS_AVAILTOTIME TIMESTAMP  ,
  RS_AVAILFROMTIME TIMESTAMP  ,
  RS_TITLE VARCHAR2 (255)  ,
  RS_DESCRIPTION VARCHAR2 (2500)  ,
  RS_STATUSBY VARCHAR2 (255)  ,
  RS_MODIFICATIONBY VARCHAR2 (255)  ,
  RS_INSIDEPORTAL NUMBER (6,0)  ,
  RS_LA_LANGCODE NUMBER (6,0)  ,
  RS_AUTHOR VARCHAR2 (100)  ,
  RS_PUBLISHER VARCHAR2 (255)  ,
  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"

In SQLPlus, if I try to run:
"select to_timestamp('2001-09-19-15.24.00.000000',
'YYYY-MM-DD-HH24.MI.SS.FF') from dual;"

then I get this result:
"TO_TIMESTAMP('2001-09-19-15.24.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')

---

19-SEP-01 03.24.00.000000000 PM" I really doesn't understand why I get this error message from SQLLoader, so any help would be great appriciated!!

Best regards,

Frode Langseth Received on Thu Aug 07 2003 - 08:53:59 CDT