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

Kin Ng wrote:

> I have a need to create a temp table for a session only. The temp
> table's structure is not fixed. Thus the current Oracle temp session
> table won't work.
>
> Let me explain why I need this. Our business requirement is to have
> dynamic product attributes. I.e. the product's attribute is user
> defined. I have a table that stores row wise for each attribute (say
> Brand) what the values will be (say Ford). Thus the user can define
> as many attributes as they need. Here is the simplified table:
>
> Product Attribute Value
> ---------------------------
> Sparkplug1 Brand Ford
> Sparkplug2 Brand GM
> Sparkplug1 Size Big
> Sparkplug2 Size Small
> Sparkplug1 Weight Heavy
> Wiper1 Brand Acura
> Wiper1 Size Long
> Wiper2 Brand GM
> Wiper2 Size Small
> Wiper3 Brand Ford
>
> Now the tricky part, the user expects to see their definition as this
> from a View that I need to create:
>
> Product Brand Size Weight
> ------------------------------------
> Sparkplug1 Ford Big Heavy
> Sparkplug2 GM Small null
> Wiper1 Acura Long null
> Wiper2 GM Small null
> Wiper3 Ford null null
>
> This is almost like a pivot table but I need to do this dynamically.
> If I can create a table (may I say like the SQL Server temp table) and
> fill this table and return it, the whole world will be happy.
>
> So how can I do this in Oracle?

1. Pipelined functions
2. Native dynamic SQL
3. DBMS_SQL built-in package

So exactly who came up with these requirements? ;-)

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)