Re: skipping fields in SQL*Loader for Oracle 8.0.5

thanks for the help. However, I do have one last question. Is there a limitation on the number of rows that I can have in the control file. When I have 20 columns to load everything is fine. But if I add another column then I get an error message stating that not all variable are bounded. Any ideas?

I tried changing the readsize and bind to a higher value but no luck. Each row is only 312 bytes. I tried reading in around 312 bytes and the 4096 bytes.

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:b94FODkl+tgzKRKJlcIKssjMJUjD_at_4ax.com...
> A copy of this was sent to "Me" <annardo_at_earthlink.net>
> (if that email address didn't require changing)
> On Wed, 13 Oct 1999 18:54:39 -0400, you wrote:
>
> >I have a some questions about using sql*loader in Oracle 8.0.5. Is it
> >possible to skip fields in a data file when specifing the fields in the
> >control file?
> >e.g.
> >fields in data file:
> >"first", "second", "third", "fourth", "fifth"
> >
>
> see the URL in my signature for a way to do this in various releases of
sqlldr.
>
> >I want to only load in the second ("second"), and fourth ("fourth")
column
> >and discarding the rest. The resulting database rows would look like:
> >"second", "fourth"
> >etc....
> >
> >Next question is...is it possible to load each field in a data file row
onto
> >its own row in the database?
> >e.g.
> >fields in data file:
> >"first", "second", "third", "fourth", " ", "sixth"
> >
> >The resulting rows in the database would look something like (note that
it
> >skips the blank fifth column):
> >"second", "first"
> >"second", "third"
> >"second", "fourth"
> >"second", "sixth"
> >
> >I've noticed that there is a FILLER option in Oracle 8i but this doesn't
> >seem to work for my 8.0.5. The datafile is in a delimited format with
each
> >column enclosed in ".
> >
>
> partially. I can show you how to load up as above but not skip the
'blank'
> fifth column.
>
> You could put a trigger on the table to clean that out or just run a
delete
> after the fact.
>
> After you read the "skipping columns with sqlldr (HOWTO)", you can use a
control
> file like:
>
> LOAD DATA
> INFILE *
> REPLACE
> INTO TABLE T
>

> FIELD1 position(1:4096) "delimited.word(:field1,2,NULL,',')",
> FIELD2 position(1:1) "delimited.word(:field1,1,NULL,',')"
> )
> INTO TABLE T
>

> FIELD1 position(1:4096) "delimited.word(:field1,2,NULL,',')",
> FIELD2 position(1:1) "delimited.word(:field1,3,NULL,',')"
> )
> INTO TABLE T
>

> FIELD1 position(1:4096) "delimited.word(:field1,2,NULL,',')",
> FIELD2 position(1:1) "delimited.word(:field1,4,NULL,',')"
> )
> INTO TABLE T
>

> FIELD1 position(1:4096) "delimited.word(:field1,2,NULL,',')",
> FIELD2 position(1:1) "delimited.word(:field1,5,NULL,',')"
> )
> INTO TABLE T
>

> FIELD1 position(1:4096) "delimited.word(:field1,2,NULL,',')",
> FIELD2 position(1:1) "delimited.word(:field1,6,NULL,',')"
> )
> BEGINDATA
> "first", "second", "third", "fourth", " ", "sixth"
>
>
> running that gives me:
>
>
> tkyte_at_8.0> select * from t;
>
> FIELD1 FIELD2
> ------------------------- -------------------------
> "second" "third"
> "second" "first"
> "second" "fourth"
> "second" " "
> "second" "sixth"
>
>
> Then, you need to clean up the rows you don't want...
>
>
>
> >TIA.
> >
> >
>
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Thu Oct 14 1999 - 20:52:20 CDT