Re: Problems with dynamic SQL in Oracle.

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:27:56 CST