Re: sqlldr problem with datetime fields

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Thu, 08 Aug 2002 12:25:49 -0600
Message-ID: <3D52B7AD.5F5A0549_at_noaa.gov>


Good work, Anurag. We all should have caught this because the SQL*Loader datatypes are clearly and unambiguously identified in the Oracle8i and Oracle9i references describing SQL*Loader. As we all know by now, Oracle9i has changed quite a bit in terms of date/time storage and expression, and this is reflected in the SQL*Loader usage.

Anyway, Jim just made a simple mistake of using Oracle9i syntax with Oracle8i software.

Bye,
Tom

Anurag wrote:

> You are right .. this baby fails in 8.1.7
>
> I guess the error message threw us off. I was on the track that in some twisted version
> of Oracle the lastmodified name is reserved word or something.
>
> The problem is the TIMESTAMP specification. Why? I don't know as yet .. and am in a hurry.
> My guess is that TIMESTAMP is a valid ctl file specification for 9i .. but not for 8i.
>
> So here is what you should try in your ctl file:
> load data
> infile 'image_pp.txt'
> into table image_pp
> fields terminated by ';' optionally enclosed by '"'
> (IMAGEID integer external,
> ELEMENTNAME char,
> OVERRIDEPATH char,
> CREATED DATE "YYYY-MM-DD HH24:MI:SS",
> LASTMODIFIED DATE "YYYY-MM-DD HH24:MI:SS",
> LASTUSER integer external
> )
>
> I replaced TIMESTAMP with DATE ... things loaded fine then
>
> Anurag
>
> "Jim Flynn" <jflynn_at_engage.com> wrote in message
> news:9747aacb.0208071346.24894a45_at_posting.google.com...
> > "Anurag" <avdbi_at_hotmail.com> wrote in message news:<ul14946jvp16b7_at_corp.supernews.com>...
> > > 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
> >
> > Anurag,
> >
> > I am running 8.1.7. I am using this command line (about as simple as you can get!)
> >
> > sqlldr control=image_pp.ctl
> >
> > I then enter username and password.
> >
> > Thanks for the effort!!

--
=====================================================
Thomas Gaines
Professional Research Assistant / Senior DBA
CIRES, NGDC/NOAA
303.497.3798  (office)
303.912.1241  (cell)
thomas.gaines_at_noaa.gov
=====================================================
Received on Thu Aug 08 2002 - 20:25:49 CEST

Original text of this message