SQL server temporary tables to oracle cursors

I have a set of stored procedures written for MS SQL server that I now need to translate into PL/SQL.

I have a table of raw data something like this:

create table rawdata

   (id int,sex1 char(1),dob1 date,sex2 char(1),dob2 date,sex3 char(1),dob3 date);

And am trying to get it into a client table;

create table client
(id int,sex char(1),dob date);

Needless to say, it's not quite that simple, and in SQL server I was using a temporary table as an intermediate step, in particular to eliminate duplicates.

In SQL server, I had a procedure that was passed a suffix so that it did something like:

exec('insert into temp_client
(id,sex,dob)

  select id,sex' || @suffix ||',dob' || @suffix || ' from rawdata')

(in a sort of bastardised syntax with SQLserver @params and Oracle concatentation operator).
This would be called (for this example) three times, with parameter values of 1,2 and 3.

So the question is: what is the best way to do this in Oracle? Should I still use dynamic SQL? If I do, how do I declare a cursor? Might BULK COLLECT give better performance, or is that impossible to use where I am trying to eliminate duplicates based on more than one column?

Thanks for any help,

Jon Received on Tue Feb 26 2002 - 08:59:46 CST