Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Loader Delimited Problem
As far as I know, sqlloader won't be able to handle that correctly. I
had the same issue and after researching and not finding an answer, I
created a little Perl script that splits the file into one file for each
table. Then I was able to just load each of them.
I'm really curious if someone found another solution for this.
Jonathan Bliss wrote:
> Hi, help would be appreciated,
>
> Problem:
>
> Pipe delimited variable length file.
>
> table1.col1 | table1.col2 | table1.col3 | table2.col1 | table2.col2 |
> table1.col4
>
> control file
>
> load data
> into table table1
> (col1,
> col2,
> col3)
> --
> into table table2
> (col1,
> col2)
> --
> into table table1
> (col4)
>
> This of course starts a new row for table1.col4, my current work around is
> to stage the data through work tables.
>
> load data
> into table table1
> (load_rec recnum,
> col1,
> col2,
> col3)
> --
> into table table2
> (col1,
> col2)
> --
> into table table1_tail
> (load_rec recnum,
> col4)
>
> And use a quick piece of SQL to add them back joining on load_rec.
> In this event it was easier than using a utility to convert from variable
> length to fixed length, which also solves this problem.
>
> Despite the above, in an ideal world, I would be able to manage it all from
> the control file, the best candidate seemed to be continueif but I haven't
> been able to apply that to this situation. Any thoughts?
>
> TIA
>
> Jonathan
>
>
>
Received on Fri Nov 29 2002 - 09:21:03 CST