Re: Get ref cursor data dynamically - HOW ????

Hi Brian,

Probably it is a misunderstanding because of my English, sorry. Here is what I was trying to do for example:

...
procedure save_cursor_data (cur_in sys_refcursor) is

 s varchar2(32767);

 procedure add_ln (txt in out varchar2) is  begin
  txt:=txt||chr(10);
 end;  

begin

   add_ln(s, 'declare');
   add_ln(s, ' type t_rec IS RECORD');
   add_ln(s, ' (');     
     << here I am itemizing up fields related to the ref cursor>>
   add_ln(s, ' );');    
   add_ln(s, ' rec t_rec;');   
   add_ln(s, 'begin');    
   add_ln(s, ' loop');   
   add_ln(s, '  fetch :cur into rec;');      
   add_ln(s, '  exit when :cur%notfound;');         
     << here I am doing whatever I want>>
   add_ln(s, ' end loop;');      
   add_ln(s, ' close :cur;');         
   add_ln(s, 'end;');          
 

   EXECUTE IMMEDIATE s USING cur_in, cur_in, cur_in; -- incorrect

end;
...

Naive attempt though; EXECUTE IMMEDIATE allows cursors as bind variables in "OUT" notation only. Dbms_sql doesn't help either etc. Actually for me it would be enough just to take the sql-statement related to the given ref cursor somehow; I'd say it's even better because I need that data for some test issues, so I can run that query once again without any application impact.

Thanks !

Steve.

"Brian E Dick" <bdick_at_cox.net> wrote in message news:<2z63a.5366$4F3.321491_at_news2.east.cox.net>...
> Cursors don't have to be bound to a %rowtype. They can be totally generic.
> The only requirement is that you have the right number and type of variables
> when you do the fetch.
>
Received on Sat Feb 15 2003 - 01:14:20 CET