Re: SQL Loader Question

Hi,
You are right. The FILLER keyword was not supported in Oracle 8.0.5. I saw the control file reference at
http://www-wnt.gsi.de/oragsidoc/doc_805/index.htm .

Do the following.
The spec for the created field should now read ... created DATE 'YYYY-MM-DD HH24:MI:SS' "RTRIM(:created,'.')" , This will chop whatever is after the '.' in the created field. Then the date mask will be applied and the field will be converted to date.

Tell me if it works

Regards

--Yash Ganthe
(Pune,India)

Eric Levinson <nospam_at_spam.levinsong.com> wrote in message news:XASa8.20169$TI3.189808_at_typhoon.sonic.net...
> Hi there1 Thanks for the feedback! Is the FILLER type specific to a
> version of Oracle?
>
> When running the script below, I get the following error message:
>
>
>
> SQL*Loader: Release 8.0.5.2.1 - Production on Thu Feb 14 9:31:30 2002
>
> (c) Copyright 1998 Oracle Corporation. All rights reserved.
>
> SQL*Loader-350: Syntax error at line 9.
> Expecting "," or ")", found "FILLER".
> dummy FILLER,
>
>
>
>
>
> "Yash" <> wrote in message news:a4fc4u$4bl$1_at_news.vsnl.net.in...
> > Hi,
> > You could make SQL*Loader believe that SSS is a field in the data
> file,
> > that does not correspond to any column of the table. For such fields,
> there
> > is the facility of the 'FILLER' keyword.
> > > LOAD DATA
> > > INFILE "/extracts/tr/file.csv"
> > > INFILE TR_CUSTOMERXREF
> > > FIELDS TERMINATED BY ','
> > > (
> > > tr_customerxrefseq ,
> > > type,
> > > created DATE 'YYYY-MM-DD HH24:MI:SS' "SUBSTR(:created,1,19)"
> > terminated by '.',
> > dummy FILLER,
> > > deleted,
> > > customerid,
> > > custseq
> > > )
> >
> > SQL*Loader will consider date upto the period and the next field to be
the
> > fractional part. This will be considered as a FILLER field and will not
be
> > loaded.
> >
> > Let me know if this works.
> > Regards
> >
> > --Yash Ganthe
> > (Pune, India)
> >
> >
> > Eric Levinson <nospam_at_spam.levinsong.com> wrote in message
> > news:VFEa8.19963$TI3.187882_at_typhoon.sonic.net...
> > > I am trying to load in a comma delimited flat file, where the a date
> > column
> > > in the file is:
> > >
> > > YYYY-MM-DD HH:MI:SS.SSS
> > >
> > > Where the .SSS is a fractional seconds. I essentially want to tell
SQL
> > > Loader to IGNORE everything after the period (since I can't seem to
find
> a
> > > TO_DATE specifier for 10 thousandths of a second)
> > >
> > > I have tried everything in the SQL Loader book for accomplishing this,
> but
> > > to no avail.
> > >
> > > Currently, my control file looks like this:
> > >
> > > LOAD DATA
> > > INFILE "/extracts/tr/file.csv"
> > > INFILE TR_CUSTOMERXREF
> > > FIELDS TERMINATED BY ','
> > > (
> > > tr_customerxrefseq ,
> > > type,
> > > created DATE 'YYYY-MM-DD HH24:MI:SS' "SUBSTR(:created,1,19)",
> > > deleted,
> > > customerid,
> > > custseq
> > > )
> > >
> > > The data looks like this:
> > >
> > > 11167125,1,2002-02-08 11:08:04.793,0,32079355,35976194
> > > 11167126,1,2002-02-08 11:08:04.793,0,32079356,35976195
> > >
> > >
> > > When this runs, it looks like I get errors telling me that CUSTSEQ is
> not
> > a
> > > number. My only guess it it is because it is trying to use the '.793'
> for
> > > the following columns.
> > >
> > > How can I make SQL Loader forget about the fractional seconds? or even
> > > better, does Oracle have a way of taking the fractional seconds?
> > >
> > > Any help would be greatly appreciated!
> > >
> > > Eric
> > >
> > > eric at levinsong dot com
> > >
> > >
> >
> >
>
>
Received on Fri Feb 15 2002 - 06:10:25 CET