X-Received: by 10.50.150.98 with SMTP id uh2mr24783377igb.2.1454499637861; Wed, 03 Feb 2016 03:40:37 -0800 (PST) X-Received: by 10.182.24.67 with SMTP id s3mr19102obf.12.1454499637832; Wed, 03 Feb 2016 03:40:37 -0800 (PST) Path: news.netfront.net!news.glorb.com!ks5no786222igb.0!news-out.google.com!kr2ni5385igb.0!nntp.google.com!ks5no786217igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Wed, 3 Feb 2016 03:40:37 -0800 (PST) In-Reply-To: <317d1b9b-7d96-445c-9d5a-468279d100f6@f36g2000hsa.googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=59.144.5.178; posting-account=_Xz0PwoAAACjFRC61TZHSw0r9LmXh_84 NNTP-Posting-Host: 59.144.5.178 References: <317d1b9b-7d96-445c-9d5a-468279d100f6@f36g2000hsa.googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: controlling order of data import through sql loader From: satpathy.archana@gmail.com Injection-Date: Wed, 03 Feb 2016 11:40:37 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: news.netfront.net comp.databases.oracle.server:2644 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