Re: Can DBMS_SQL return a cursor ?

Thank you very much !

Thomas Kyte wrote:

> 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;
> 15 end;
> 16 /
>
> 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
Received on Sun Feb 21 1999 - 18:19:21 CET