comments embedded
--
"Kin Ng" <kin_ng5_at_yahoo.com> wrote in message
news:d5b3f600.0307210859.67ef65c2_at_posting.google.com...
> Daniel,
>
<snip>>
> 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
Hopefully you are using bind variables. If not you are just causing
yourself great pain and contributing to making the application totally
unscalable.(never mind parse once and execte many times and array inteface)
My bet is that you are going to end up with a very unscalable application
and of course blame it on Oracle.
Jim
<snip>
>
> 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?
Received on Mon Jul 21 2003 - 20:23:43 CDT
