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

Kin Ng wrote:

> 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? ;-)

Then why ignore my second and third suggestions and only look at the first?

--
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)