Re: Can DBMS_SQL return a cursor ?

[Quoted] A copy of this was sent to "L.Wang" <L.Wang_at_cable.a2000.nl> (if that email address didn't require changing) On Sat, 20 Feb 1999 20:39:34 +0100, you wrote:

>Hi! I would like to use a 'SELECT' in DBMS_SQL package and have the
>result back in a cursor.
>
>So its like procedrue A calls procedure B with two parameters, one
>contains a value which B will use to execute a 'SELECT' with DBMS_SQL,
>another parameter is a cursor that B will use to return the result of
>the selection to A.
>
>Anyone know how to do it ? Thanks !
>
>
>Ludi Wang

you can return the 'cursor' from a dbms_sql call from procedure b to procedure a. It won't be a plsql cursor (eg: you will not be able to use fetch ThatCursor into variable list), it will be a cursor that dbms_sql can operate on.

In Oracle8i, you will be able to do the following (but not before, there is no way to do this before 8.1)

SQL> create or replace package types
  2 as
  3 type refCur is ref cursor;
  4 end;
  5 /

Package created.

SQL>
SQL> create or replace procedure b( p_query in varchar2, p_c1 in out types.refCur )
  2 as
  3 begin
  4 open p_c1 for p_query;
  5 end;
  6 /

Procedure created.

SQL>
SQL> create or replace procedure a
  2 as

  3          l_c1    types.refCur;
  4          emp_rec emp%rowtype;
  5  begin
  6          b( 'select * from emp where rownum < 5', l_c1 );
  7  
  8          loop
  9                  fetch l_c1 into emp_rec;
 10                  exit when l_c1%notfound;
 11  
 12                  dbms_output.put_line( emp_rec.ename );
 13          end loop;
 14          close l_c1;

Procedure created.

SQL>
SQL> exec a
SMITH
ALLEN
WARD
JONES PL/SQL procedure successfully completed.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation