RE: SQL*Loader Help -- Multiple rows into single column

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 26 Jun 2003 13:42:48 -0700
Message-ID: <F001.005BAB57.20030626125640@fatcity.com>

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=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).
--

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
---------------------------------------------------------------------