Re: controlling order of data import through sql loader

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

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

Thanks,
Archana Received on Wed Feb 03 2016 - 12:40:37 CET