Re: SQL*Loader Delimited Problem

Hi,

Jonathan, you can load all fields into table1 firstly using FILLER field. Something like this:

load data
into table table1
(col1,
col2,
skip_t2_col1 filler,
skip_t2_col2 filler,
col3)

And now then:

into table table2
(skip_t1_col1 filler position(1),
skip_t1_col2 filler,
skip_t1_col3 filler,
col1,
col2)

This is the main idea above. Possibly I missed some details and you have to invistigate it a bit.
Hope this helps.

Regards,

--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com

"Jonathan Bliss" <bliss_jonathan_at_hotmail.com> wrote in message
news:tOGF9.413$iY5.53768_at_newsfep1-win.server.ntli.net...


> 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

>

>

>