Re: Problems with dynamic SQL in Oracle.

Hello, Sean!

Thank you for the links! Reading them proved to be very illuminating. The first example seems to show the "disconnect" I was having: once the ref cursor is declared and defined, *how* is it returned as a "native" result set? Thanks to the example, it looks like that's accomplished by the PRINT command (to display the refcursor variable, presumably).

Curiously, in my Oracle PL/SQL book, there isn't an index entry for a PRINT command. I'm still a little "fuzzy" with how that works, but I'll give that a try first thing in the morning. ;-)

Thanks again for your help!

John Peterson

"sean zhang" <sean_at_cnfei.com> wrote in message news:zuyw6.34$pH.5167_at_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 - 22:39:00 CST