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).
Received on Fri Jun 27 2003 - 08:19:06 CDT
