Gurus, I have a special scenario to load data into tables with SQL*Loader.
My SQL Loader data is not fixed format. It changes from time to time. But there is a good pattern about the data. Data which starts with letter "R" should go to table1 and data which starts with letter "Z" should go to table2. Table1 data is always fixed format where as table2 is kind of tricky. I would like to load data which starts with letter "Z" into table2 as a single row. For Example: (see my data at the bottom) Tom is having 3 lines of data Bob and Sam is having 4 lines of data Joe is having 5 lines of data Right now as per my SQL Loader Control file (sql_load.ctl), all the data which starts with Z goes into different rows (Tom --> 3 rows, Bob and Sam --> 4 rows, Joe --> 5 rows). I would like to load four rows into table1 (it is fine) and four rows into table2 (I am getting 16 rows). I hope I explained properly. FYI Please take look at the following scripts. -- Create Tables Script Start (sql_load.sql) -- Sequence create sequence table1_seq increment by 1 start with 1; create sequence table2_seq increment by 1 start with 1; -- Tables create table table1 (serial_no number(5), name varchar2(10), amount number(4)) / create table table2 (shipment_no number(5), details varchar2(1000)) / -- Create Tables Script End (sql_load.sql) -- SQL Loader Control file Start (sql_load.ctl) options (rows=1, errors=10000) load data infile 'c:\sql_load.txt' badfile 'c:\sql_load.bad' discardfile 'c:\sql_load.disc' replace -- load table1 into table table1 when (1:1) = 'R' (serial_no position(1:1) "table1_seq.nextval", name position(2:6) char, amount position(6:10) char) -- load table2 into table table2 when (1:1) = 'Z' (shipment_no position(1:1) "table2_seq.nextval", details position(2:81) char) -- SQL Loader Control file End (sql_load.ctl) -- SQL Loader Data File Start (sql_load.txt) RTom 400 ZName: Tom ZShip_To: New York ZBill_To: Trenton RBob 300 ZName: Bob ZShip_To: Chicago ZBill_To: Detroit ZNotes: Best Customer Award RSam 500 ZName: Sam ZShip_To: Troy ZBill_To: Dallas ZNotes: Average Customer Award RJoe 200 ZName: Joe ZShip_To: Erie ZBill_To: San Fransisco ZNotes: Best Customer Award ZSpecial Notes: Include Customer -- SQL Loader Data File End (sql_load.txt) Thanks, Bob __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
