Re: Need SQL Server Temp Table equivalent (challenge!)

On 22 Jul 2003, kin_ng5_at_yahoo.com wrote:
> Galen,
>
> Thinks for looking very detail in my example. You are right I
> can't do a stright Select * from MyView in Oracle. If I have
> to do that in SQL Server, I have to call the SP to get the same
> result. I was just trying to illustrate what I want. I was
> hoping the Table Function of something that allows me to do
> something to that effect.

But, now, I'm not sure what you want.

Is there code that executes against SQLServer that you are trying to migrate to Oracle with no/or little change? Is this code a stored procedure that gets executed and within this stored proc, you use a temp table?

Or, are you just looking for a way to do this in Oracle?

> If you never need a temp table in Oracle, can you suggest how I
> can do that in Oracle without temp table?

Well, Tom Kyte shows how to pivot a result set at

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:124812348063

So, within a proc, I would select the distinct attribute types into a cursor, and then walk through this cursor building a string variable that holds the SQL representing a pivot query that you read about in the above link. I would then "execute immediate" that string returning a ref cursor to the calling program.

-- 
Galen Boyer