Re: controlling order of data import through sql loader

Comments embedded.
On Apr 14, 1:18 pm, tklarr <tkl..._at_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.

>
> 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

David Fitzjarrell Received on Mon Apr 14 2008 - 14:26:09 CDT