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

I studied the pipelined function and table function and found that each required a **pre-defined" type for the return. The key for my need is variable columns, not pre-defined.

e.g. I want to return 2 columns by doing this:

Select * from MyView

At a later time, the same stmnt may need to return 3 columns.

So I still can't see how that can be done in oracle.

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F11EAB3.E321E65D_at_exxesolutions.com>...
> 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? ;-)
Received on Thu Jul 17 2003 - 19:54:08 CDT