sqlldr problem with datetime fields

From: Jim Flynn <jflynn_at_engage.com>
Date: 5 Aug 2002 11:48:27 -0700
Message-ID: <9747aacb.0208051048.2e2f12a2_at_posting.google.com>



Hi,

[Quoted] I am trying to load a table with records like this:

1;"194282.jpg";"http://genesis:8080/smartmedia/servlet/smartmediaservlet?type=Image&res=thumb&ref=194282.jpg";2002-04-26 [Quoted] 00:00:00;2002-04-26 00:00:00;1
2;"205872.jpg";"http://genesis:8080/smartmedia/servlet/smartmediaservlet?type=Image&res=thumb&ref=205872.jpg";2002-04-26 00:00:00;2002-04-26 00:00:00;1
3;"206912.jpg";"http://genesis:8080/smartmedia/servlet/smartmediaservlet?type=Image&res=thumb&ref=206912.jpg";2002-04-26 00:00:00;2002-04-26 00:00:00;1

into a table like this:
SQL> describe image_pp

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 IMAGEID                                   NOT NULL NUMBER(10)
 ELEMENTNAME                               NOT NULL VARCHAR2(30)
 OVERRIDEPATH                                       VARCHAR2(255)
 CREATED                                   NOT NULL DATE
 LASTMODIFIED                              NOT NULL DATE
 LASTUSER                                  NOT NULL NUMBER(10)

Here is the control file I have:

speedy% cat image_pp.ctl
load data
infile 'image_pp.txt'
into table image_pp
fields terminated by ';' optionally enclosed by '"' (IMAGEID integer external,
 ELEMENTNAME char,
 OVERRIDEPATH char,
 CREATED TIMESTAMP "YYYY-MM-DD HH24:MI:SS",  LASTMODIFIED TIMESTAMP "YYYY-MM-DD HH24:MI:SS",  LASTUSER integer external
)

When I run this control file, I get the following errors in the log file:

Table IMAGE_PP, loaded from every logical record. Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
IMAGEID                             FIRST     *   ;  O(") CHARACTER
ELEMENTNAME                          NEXT     *   ;  O(") CHARACTER
OVERRIDEPATH                         NEXT     *   ;  O(") CHARACTER
CREATED                              NEXT     *   ;  O(") DATETIME
YYYY-MM-DD HH24:MI:SS
LASTMODIFIED                         NEXT     *   ;  O(") DATETIME
YYYY-MM-DD HH24:MI:SS
LASTUSER                             NEXT     *   ;  O(") CHARACTER

Record 1: Rejected - Error on table IMAGE_PP, column LASTMODIFIED. ORA-00904: invalid column name

Record 2: Rejected - Error on table IMAGE_PP, column LASTMODIFIED. ORA-00904: invalid column name

Record 3: Rejected - Error on table IMAGE_PP, column LASTMODIFIED. ORA-00904: invalid column name

Anyone have any suggestions? Why is LASTMODIFIED being identifed as an invalid column? I've looked through the utilities manual, and I think have have the timestamps properly formatted.

Thanks in advance...

--
Jim Flynn
Engage, Inc
Received on Mon Aug 05 2002 - 20:48:27 CEST

Original text of this message