Re: Problems with dynamic SQL in Oracle.

Thomas, Peter, and the rest, thank you so much for the additional information! I haven't had an opportunity to try this out today (I've been stuck on a database dump/restore issue today ;-). But, as soon as I'm able, I hope to familiarize myself with this aspect.

Thank you again! :-)

John Peterson

"Thomas Olszewicki" <ThomasO_at_noSpm.cpas.com> wrote in message news:FcRw6.125128$x27.6743132_at_news1.rdc2.on.home.com...
> John,
> It is in your help file:
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> DECLARE
> TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
> emp_cv EmpCurTyp; -- declare cursor variable
> my_ename VARCHAR2(15);
> my_sal NUMBER := 1000;
> cName VARCHAR2(30);
> BEGIN
> cName := 'MYTABLE'
> OPEN emp_cv FOR 'SELECT ename, sal FROM'||cName||' WHERE sal > :s'
 USING
> my_sal;
> ...
> END;
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> You can find it in Oracle docs : Chapter 10 - Native Dynamic SQL
> Look up : Execute immediate for DDL and DML and OPEN ... FOR for queries.
> HTH
> Thomas Olszewicki
>
>
> "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 Thu Mar 29 2001 - 19:31:47 CST