From oracle-l-bounce@freelists.org Tue Apr 19 16:48:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3JLmV98003963 for ; Tue, 19 Apr 2005 16:48:31 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3JLmU4Z003959 for ; Tue, 19 Apr 2005 16:48:30 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 97FE7185640; Tue, 19 Apr 2005 15:46:12 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11850-08; Tue, 19 Apr 2005 15:46:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1C2C118558F; Tue, 19 Apr 2005 15:46:12 -0500 (EST) Message-ID: <17ECCBDCF27C544583F2CAD928F953260221FBCA@memex1.corp.cefs.int> From: "Knight, Jon" To: "'oracle-l@freelists.org'" Subject: SQLLDR - ID in header rec only Date: Tue, 19 Apr 2005 15:44:22 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 18626 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jknight@concordefs.com Precedence: normal Reply-To: jknight@concordefs.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 All, I'm stumped. We're trying to load some data that we receive in the following format: Header Record 1 Header Record 2 Detail Record 1 Detail Record 2 ... Detail Record N Trailer Record Our first thought was to use multiple "insert into" clauses in the .ctl file to insert the header records and detail records into separate tables. Unfortunately, the detail records don't contain the header ID! Next we tried the CONTINUEIF clause to concatenate into one logical record so all the subsequent "insert into" clauses would have access to the header ID. Below is a script that demonstrates our efforts. This works as expected, however our particular file may potentially contain thousands of detail records for each header/trailer. So the .ctl file would get rather unwieldy. Even if we could get a huge .ctl file to work, we would still have the performance hit of discarding all those empty records from the multiple "insert into" clauses on short logical records. What seems ideal is to skip the CONTINUEIF clause and find some way to ask SQLLDR to remember the ID from the header record and use it while inserting each detail record, until reaching a trailer or a new header record. But, my research has failed to discover any such a construct. It's probably right there in front of me, so whoever can see it gets to smear egg on my face. Any suggestions are welcome. Thanks for looking. Thanks, Jon Knight create table family ( id number(10) ,last_name varchar2(30) ) / create table family_members ( fam_id number(10) ,first_name varchar2(30) ) / ----- Data File ----- 1 **Smith **Samuel **Cindy **Sandy 2 **King **Camden **Cody **Kim **Courtney 3 **Johnson **Jason **Jennifer ----- EOF ----- ----- SQL Loader CTL file ----- load data infile 'data.txt' replace continueif next (1:2) = '**' into table family ( id position( 1:10) char ,last_name position(11:20) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(21:30) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(31:40) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(41:50) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(51:60) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(61:70) char ) ----- EOF ----- -- http://www.freelists.org/webpage/oracle-l