controlling order of data import through sql loader

[tklarr]

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 - H and then muliple D lines and then the H line
again. I am trying to get the sequenced ID number assigned at the
header to follow the detail. I assign the H line the nextval and then
the detail the currval as the link between the two tables. However,
the data loading process would need to keep to the order of the
physical data file for this to work. Is there a way I can force this
behaviour?

Here is a copy of my current control file.

OPTIONS (silent=(discards))
LOAD DATA
infile 'data/edi_orders.txt'
badfile edi_order.bad
discardfile edi_order.dsc
discardmax 999
APPEND
into table edi.edi_order
when rec_type = 'H'
trailing nullcols
( rec_type FILLER char(1) terminated by '{',
edi_order_id integer "edi.edi_order_id_seq.NEXTVAL",
trading_partner_name terminated by '{',
trading_partner_id terminated by '{',
purchase_order terminated by '{',
rel_number terminated by '{',
currency_code terminated by '{',
buyer_desc terminated by '{',
supplier_desc terminated by '{',
fob_desc terminated by '{',
terms terminated by '{',
date_order date(10) "MM/DD/YYYY" terminated by '{',
message terminated by '{',
billto_name terminated by '{',
billto_addr1 terminated by '{',
billto_city terminated by '{',
billto_state terminated by '{',
billto_postal terminated by '{',
billship_name terminated by '{',
billship_addr1 terminated by '{',
billship_city terminated by '{',
billship_state terminated by '{',
billship_postal terminated by '{',
vendor_name terminated by '{',
vendor_addr1 terminated by '{',
vendor_city terminated by '{',
vendor_state terminated by '{',
vendor_postal terminated by '{',
date_delivery_requested date(10) "MM/DD/YYYY" terminated by '{',
shipto_desc1 terminated by '{',
shipto_desc2 terminated by '{',
shipto_code terminated by '{',
shipto_addr1 terminated by '{',
shipto_addr2 terminated by '{',
shipto_city terminated by '{',
shipto_state terminated by '{',
shipto_postal terminated by '{',
date_processed date(10) "MM/DD/YYYY" TERMINATED BY WHITESPACE,
loadseq SEQUENCE(MAX,1))
into table edi.edi_order_detail
when rec_type = 'D'
trailing nullcols
( rec_type FILLER POSITION(1) char(1) terminated by '{',
edi_order_id integer "edi.edi_order_id_seq.CURRVAL",
purchase_order terminated by '{',
order_line_number terminated by '{',
qty_order terminated by '{',
unit terminated by '{',
unit_price terminated by '{',
item_code terminated by '{',
item_desc terminated by '{',
ship_num terminated by '{',
reference terminated by '{',
fob terminated by '{',
date_delivery_requested date(10) "MM/DD/YYYY" terminated by '{',
transport terminated by '{',
message terminated by '{',
date_processed date(10) "MM/DD/YYYY" TERMINATED BY WHITESPACE,
loadseq SEQUENCE(MAX,1))

Thank you.
Theresa

[ora...@msn.com]

Comments embedded.

On Apr 14, 1:18 pm, tklarr <tkl...@gmail.com> 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 - H and then muliple D lines and then the H line
> again.  

Unless 11g behaves differently it won't happen, as SQL*Loader scans
the datafile for the first encountered conditional 'flag' and
processes those records first, and follows, in order, as you've
written the WHEN clauses. The data will be read 'in order', it just
won't be processed (inserted) in order, meaning SQL*Loader won't
switch tables in mid-stream to insert data as it finds it.

> I am trying to get the sequenced ID number assigned at the
> header to follow the detail.  I assign the H line the nextval and then
> the detail the currval as the link between the two tables.  However,
> the data loading process would need to keep to the order of the
> physical data file for this to work.  Is there a way I can force this
> behaviour?

Sorry to say, no.

David Fitzjarrell

[satpathy...@gmail.com]

Hi Theresa,
I have the same issue... Did you find any resolution?

Thanks,
Archana

[joel garry]

Sequences are only to enforce uniqueness, not to actually have an uninterrupted or coordinated sequential order. Search for explanations of that on asktom.oracle.com.

You may want to preprocess in another field with the sequence you desire. If this needs to match up with some sequence used by data already in the db, you'll have to adjust the sequence before you start to be beyond the ones you add, or some more complicated scheme if your existing data and app require it.

jg
--
@home.com is bogus.
https://www.publictechnology.net/articles/news/uk-government-hails-increased-oracle-investment-uk-datacentre

[Ed Prochak]

On Wednesday, February 3, 2016 at 6:40:45 AM UTC-5, satpathy...@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

[Krusty]

Ed Prochak wrote:

> On Wednesday, February 3, 2016 at 6:40:45 AM UTC-5,
> satpathy...@gmail.com wrote:
> > On Monday, April 14, 2008 at 11:48:02 PM UTC+5:30, tklarr wrote:
> > >
> > > 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?

I'm guessing Theresa probably found a solution during the 8 years since
she asked the question....

--
Preston