sqlldr problem with datetime fields
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, IncReceived on Mon Aug 05 2002 - 20:48:27 CEST