Re: Q: Dinamic OPEN v_cursor FOR SELECT ...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 26 Feb 1999 19:10:52 GMT
Message-ID: <36def1a8.21663690@192.86.155.100>

A copy of this was sent to Gombos Bertalan <bgombos_at_freemail.c3.hu> (if that email address didn't require changing) On Fri, 26 Feb 1999 17:00:51 +0000, you wrote:

>Thomas Kyte wrote:
>>
>> A copy of this was sent to Gombos Bertalan <bgombos_at_freemail.c3.hu>
>> (if that email address didn't require changing)
>> On Fri, 26 Feb 1999 13:16:10 +0000, you wrote:
>>
>> >Hello all,
>> >
>> > I would like to execute dinamical an anonymous PL/SQL block like
>> this:
>> >
>> >DECLARE
>> >BEGIN
>> > OPEN :v_ret FOR
>> > SELECT ...;
>> >END;
[snip]

>Thanks for your reply. Yes, but I would like it from PL/SQL. For
>example:
>
>type t_cref is ref cursor;
>...
>procedure get_cursor_ref(
> v_cref out t_cref,
> p_other varchar2) is
> v_block varchar2(2000);
> v_cursor integer;
> v_ret integer;
>begin
> v_block := 'BEGIN OPEN :x FOR SELECT * FROM ... WHERE ... AND ' ||
> p_other || '; END;';
> v_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(v_cursor, v_block, DBMS_SQL.V7);
> -- I should use there dbms_sql.bind_variable() but there's no version
> -- for ref cursor type
> v_ret := dbms_sql.execute(v_cursor);
> -- I should use there dbms_sql.variable_value() but there's no version
> -- for ref cursor type
> dbms_sql.close_cursor(v_cursor);
>end;
>
>Any advice is welcome, Bye:

sorry, won't be able to do that until Oracle8i. Prior to that, it is physically impossible.

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 Fri Feb 26 1999 - 13:10:52 CST