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

Kin Ng wrote:

> Daniel,
>
> Sorry I didn't mention it but your 2nd and 3rd options revolved around
> dyanmic sql which frankly didn't address the main issue. Here is what
> I think Oracle is lacking in the temp table features of SQL Server:
> dynamic table creation with dynamic columns and types AND AND AND
> returns data with this newly created table AND AND this table will be
> deleted when the session ends. This is really an old IBM concept
> where each session has it's own temp area that you can create whatever
> you want and the SYSTEM will clean up your mess at the end of the
> session (IBM's job). If I put this dynamic thing in the SP, I expect
> the table can be created with different columns each time it is
> called.
>
> Or may be I am completely wrong in Dynamic SQL (btw, I am using .NET's
> C# to call Oracle so most of the calls are already dynamic SQL) or
> DBMS_SQL but from my past (I have to say limited) experience plus
> newly reviewed DBMS_SQL doc I coudn't see how you can do that. Would
> you mind giving a pesudo code on how to do that?
>
> Thanks
>
> Kin
>
> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F182B57.6AD1248C_at_exxesolutions.com>...
> > 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?

Please don't advertise your ignorance as Oracle's lack of capability. One can easily create temporary tables with dynamically created columns in Oracle. One can also be frequently be reminded that it is bad design that only exists
in SQL Server because of the weaknesses of the underlying architecture and that Microsoft is working like crazy to
overcome it in future versions.

If you want to do it ... Native Dynaic SQL. Something I have now suggested three times.

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