Re: SQL*Loader Help -- Multiple rows into single column
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=1) 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).
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=1) 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).
RE: SQL*Loader Help -- Multiple rows into single column
Bob If you were on Unix, I would suggest you have a script that split your input file into multiple files using one of the Unix utilities, then executed SQL*Loader against each of those. Since you are on Windows, perhaps someone has a suggestion that will apply there. I know there are Windows versions of the Unix utilities floating around. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, June 26, 2003 3:40 PM To: Multiple recipients of list ORACLE-L 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=1) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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).
Re: SQL*Loader Help -- Multiple rows into single column
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=1) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
