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: sean zhang <sean_at_cnfei.com>
Date: Wed, 28 Mar 2001 22:59:52 -0500
Message-ID: <zuyw6.34$pH.5167@sydney.visi.net>

Hi, John,

I found a few references on ref-cursor on asktom.oracle.com,please just do a searrch for ref cursor, here is one of the links:

http://asktom.oracle.com/pls/ask/f?p=4950:8:31004::NO::F4950_P8_DISPLAYID,F4 950_P8_CRITERIA:701224968028,{ref}%20and%20{cursor}

Also please see this site for more examples: http://www.oracle.com/oramag/oracle/01-mar/index.html?o21sql.html   which has links to all introduction to NDS.

This can also help you:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:152600594 4436

hope this helps.

Sean zhang
sean_at_cnfei.com

"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 - 21:59:52 CST

Original text of this message

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