Re: controlling order of data import through sql loader

On Wednesday, February 3, 2016 at 3:40:45 AM UTC-8, 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 - 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

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

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