Re: Problems with dynamic SQL in Oracle.

Hmmm...I tried getting to http://oracle.technet.com and http://www.oracle.technet.com, but neither of those links seem to work. Is that an URL reference you were pointing me to?

Thanks, Jim!

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tc51vtgp886ib2_at_corp.supernews.com...
> Hello, Jim!
>
> Sorry about the oft-repeated question. I did a quick Google search and I
> think my search cast too wide a net, as it wasn't apparent that it could
 be
> done. I did find the other aspects, though.
>
> Heh! I hear you with respect to "just because you could do it in SQL
 Server
> doesn't make it necessarily a good idea to do it in Oracle", but my SQL
> Server experience at least gives me a baseline of what might be possible
> with Oracle.
>
> Thanks for your help, and I'll look for "ref cursor", whatever that is.
> Hmmm...scanning this newsgroup over the past several days, I don't see any
> instances of this question. Could you point me to one of these examples?
>
> Thanks again!
>
> John Peterson
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> news:gkvw6.637664$U46.19568380_at_news1.sttls1.wa.home.com...
> > This gets asked almost every other day in this ng. Look for ref cursor.
> > There are a ton of examples in this newsgroup. or go to
 oracle.technet.com
> > But yes you can do this.
> >
> > One word of caution. Just because you could do it in SQLServer does not
> > make it neccessarily a good idea to do it in Oracle.
> > Jim
> >
> > "John Peterson" <johnp_at_azstarnet.com> wrote in message
> > news:tc4rt0f42k2i46_at_corp.supernews.com...
> > > Hello, all!
> > >
> > > I am using Oracle 8.1.7 on Windows 2000 Professional. I am a newcomer
 to
> > > Oracle, having spent a lot of time with Microsoft SQL Server.
> > >
> > > I am trying to cobble together some dynamic SQL in Oracle and I have
 run
> > > into some confusion. I think I understand that Oracle provides
 several
> > > mechanisms with which to execute dynamic SQL:
> > >
> > > * The DBMS_SQL package.
> > > * The use of Native Dynamic SQL (NDS).
> > > * Through the EXECUTE IMMEDIATE command.
> > >
> > > However, as I explore each of these techniques, it doesn't seem clear
 to
 me
> > > that any of these methods return a "normal" result set. Is there any
 way
 to
> > > write PL/SQL code to treat a dynamic query which yields a multi-row
 result
> > > set as a regular result set that can be handled by the calling
 application?
> > >
> > > Let me explain. In SQL Server, I might be able to do something like
 the
> > > following:
> > >
> > > declare @TableName
> > > set @TableName = 'dual'
> > > execute('select * from ' + @TableName)
> > >
> > > And this will natively return a result set to whatever client issued
 the
> > > command.
> > >
> > > From what I can tell from the Oracle documentation (and testing),
 there
> > > appear to be some significant hoops with getting Oracle to handle a
> > > multi-row result set when dynamic SQL is used. It looks like it
 forces
 the
> > > programmer to handle a bunch of cursoring aspects. That's fine if I
 want
 to
> > > handle the results row-at-a-time, but I really just want to return the
> > > results of the dynamic SQL as a "normal" result set and let the
 calling
> > > application process it as if they had submitted some standard DML.
> > >
> > > I would greatly appreciate any clarification, ideas, or suggestions!
 Thank
> > > you! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
  Received on Wed Mar 28 2001 - 19:00:37 CST