Re: Problems with dynamic SQL in Oracle.

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:21:41 CST