No. We are still with 8i. --- [EMAIL PROTECTED] wrote: > Are you on 9i? > > If so, setup the sql_load.txt file as an external > table, and you > can then use SQL and/ora PL/SQL to load your table > the > way you would like. > > Don't think you can do what you're asking directly > from sqlldr. > > Jared > > > > > > > Bob Robert <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 06/26/2003 01:39 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: SQL*Loader Help -- Multiple > rows into single column > > > 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). > > >
__________________________________ 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).
