controlling order of data import through sql loader

From: tklarr <tklarr_at_gmail.com>
Date: Mon, 14 Apr 2008 11:18:02 -0700 (PDT)
Message-ID: <317d1b9b-7d96-445c-9d5a-468279d100f6@f36g2000hsa.googlegroups.com>

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 Received on Mon Apr 14 2008 - 13:18:02 CDT