RE: Need SQL*Loader Coding Help

From: Mercadante, Thomas F <Thomas.Mercadante_at_Labor.State.Ny.Us>
Date: Mon, 14 Jan 2002 08:38:24 -0800
Message-ID: <F001.003EF315.20020114075521@fatcity.com>

Ken,

IMHO, you will need to load the data into a temp table, and process the data using a PL/SQL block. Just a couple of questions:

Are there ALWAYS 10 cnum values? for every RegNumber value?

If so, your PL/SQL block could look like:

declare
cursor c1
select reg_number from temp_table;
rec_count number;
ten_count number;

reg_number number;
c_num number;
begin

  open c1;
  fetch c1 into reg_number;
  while c1%found loop
    ten_count := 1;
    while ten_count < 10 loop

      fetch c1 into c_num;
      insert into Trademark_Class_Data(Registration_Number,Class_Code_ID)
             values(reg_number,c_num);
      ten_count := ten_count + 1;

    end while;
    fetch c1 into reg_number;
    if c1%notfound then

       exit;
    end if;
  end loop;

close c1;

good luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, January 14, 2002 8:25 AM
To: Multiple recipients of list ORACLE-L

I need some help coding this scenario. I have multiple records coming in a delimited flat file. Some of the records are:

RegNumber
Cnum1
Cnum2

.
.
.

Cnum10

There can be data in 1 to 10 of these fields.

I need to load this into this table.
Trademark_Class_Data
Registration_Number (FK)
Class_Code_ID (FK)

An example of the code.
RegNumber - 65304

Cnum1 - 45
Cnum2 - 54
Cnum3 - 100

So, my question is how do I code this so that I create 3 records in this table as?

Registration_Number     Class_Code_ID
65304                   45
65304                   54
65304                   100

I need to take data that is contained in 1 record and load it into 3 records.

Any help you can give me will be greatly appreciated.

Thanks,

Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ken Janusz
INET: ken.janusz_at_sufsys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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.com
--

Author: Mercadante, Thomas F
INET: Thomas.Mercadante_at_Labor.State.Ny.Us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------