Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Problems with dynamic SQL in Oracle.

Re: Problems with dynamic SQL in Oracle.

From: John Peterson <johnp_at_azstarnet.com>
Date: Wed, 28 Mar 2001 17:55:21 -0700
Message-ID: <tc51vtgp886ib2@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 - 18:55:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US