Problems with dynamic SQL in Oracle.

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 - 17:11:24 CST