Re: controlling order of data import through sql loader

On Wednesday, February 3, 2016 at 6:40:45 AM UTC-5, satpathy..._at_gmail.com wrote:
> On Monday, April 14, 2008 at 11:48:02 PM UTC+5:30, tklarr wrote:
> > I have a data file that has Header and Detail information.
> > The Header
> > information is put into one table and the detail into another.
> > The
> > lines are distinguised by an H or a D. Currently the sqlldr
> > reads all
> > the H lines first and puts them into the table and then
> > the D lines
> > and puts them into the other table. I would like to force
> > it to do the
> > lines in order []
> >
> > Thank you.
> > Theresa
>
> Hi Theresa,
> I have the same issue... Did you find any resolution?
>
> Thanks,
> Archana

Theresa,

Is there anything in the data that ties the header to the detail? It looks like purchase_order (surprised?).

Then my approach would be to use a staging table. the staging table would have columns to include rec_type and all the columns of both types of records. Personally, I would use another language to do this step, because I could add input line numbers under my control. But using a sequence should work to preserve the original ordering. You should not care if the sequence numbers go

     1 2 3 5 6 7 9 ...
only that they increase with each row loaded. Using one table means SQL*Loader should read the file in order.

Then parse the records and load them in the final tables using PLSQL procedures.

select the header rows ordered by purchase_order  from the staging table.
insert the header in the header table.
select the detail rows FOR THAT purchase_order  from the staging table.
Insert the detail into the detail table.

This simple approach can execute slower, but gives you more control.

(Remember, you can optimize a correct procedure but a fast procedure with wrong results always is a loser.)

There are other advantages to this approach.

HTH,
   Ed Received on Thu Feb 04 2016 - 21:37:43 CET