Re: SQL Loader Question

From: Eric Levinson <nospam_at_spam.levinsong.com>
Date: Thu, 14 Feb 2002 17:36:55 GMT
Message-ID: <XASa8.20169$TI3.189808_at_typhoon.sonic.net>

[Quoted] 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 Thu Feb 14 2002 - 18:36:55 CET