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

From: Gombos Bertalan <bgombos_at_freemail.c3.hu>
Date: Fri, 26 Feb 1999 17:00:51 +0000
Message-ID: <36D6D343.56097A58@freemail.c3.hu>

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;
> >
> > .. where type of v_ret is cursor reference.
> >There isn't comfortable version of dbmq_sql.variable_value procedure.
> >I don't have any solution, is it possible in any way?
> >
> >Bye:
>
> what doesn't work? consider:
> Connected to:
>
> SQL> variable x refcursor
>
> SQL> begin
> 2 open :x for select * from emp;
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
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:
--

    .---,                            G o m b o s  B e r t a l a n
    :   :
    :---'-. ,---. ,--. ,-.-.       rendszerfejleszto, Oracle mernok
    :     : :--   :      ;          mailto:bgombos_at_freemail.c3.hu
   ,' '---' '---' '      `-'
-- : ------------------------- Az elet megis egy habostorta? --- '10^2-1

Received on Fri Feb 26 1999 - 11:00:51 CST