Re: SQL server temporary tables to oracle cursors

In Oracle you NEVER EVER create tables inside of stored procedures. It is not that you can't but rather that there is no need to. Oracle is not just SQL Server with another company's name on the CD. The architecture, transactions, etc. are all very different.

To work in Oracle you must learn how Oracle works ... and more importantly how it does not work. Unless, of course, you wish to create slow, unscalable, insecure junk.

I strongly urge you to purchase Tom Kyte's book "expert one-on-one Oracle" and read it. Especially the first three chapters.

Daniel Morgan

Jon Waterhouse wrote:

> 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 - 15:02:44 CST