Re: sqlldr problem with datetime fields

From: Anurag <avdbi_at_hotmail.com>
Date: Tue, 6 Aug 2002 23:16:49 -0400
Message-ID: <ul14946jvp16b7_at_corp.supernews.com>


[Quoted] [Quoted] I did test your scripts on a 9.2 version Oracle and it ran fine. I can check on 8.16 and 8.1.7 versions also .. but frankly I don't expect sqlldr to fail this load in any of these versions. Please state your Oracle version and state what command line parameters you are passing to sqlldr.

Anurag

"Jim Flynn" <jflynn_at_engage.com> wrote in message news:9747aacb.0208051048.2e2f12a2_at_posting.google.com...
> Hi,
>
> 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=19 4282.jpg";2002-04-26
> 00:00:00;2002-04-26 00:00:00;1
>

2;"205872.jpg";"http://genesis:8080/smartmedia/servlet/smartmediaservlet?type=Image&res=thumb&ref=20 5872.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=20 6912.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 Wed Aug 07 2002 - 05:16:49 CEST

Original text of this message