René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Dynamic cursors in PL/SQL

create or replace package dynamic_cursor is

  type t_crs is ref cursor;

  procedure dyn_sel (
       tab_name   in varchar2,
       field_name in varchar2,
       val        in varchar2,
       crs        in out t_crs);

  procedure openCursor;

end dynamic_cursor;             
/

create or replace package body dynamic_cursor as
   procedure dyn_sel (
          tab_name   in varchar2,
          field_name in varchar2,
          val        in varchar2,
          crs        in out t_crs)

   is
     stmt varchar2(100);
   begin
     stmt := 'select * from ' || tab_name || ' where ' || field_name || ' = :1 ';

     open crs for stmt using val;
   end dyn_sel;

   procedure openCursor is
     tc t_crs;
     f1 varchar2(50);
     f2 varchar2(50);
   begin
     dyn_sel('test_for_cursor','a','two',tc);
     loop
       fetch tc into f1,f2;
       exit when tc%notfound;
       dbms_output.put_line(f2);
     end loop;
   end openCursor;
     
end dynamic_cursor;     
/

begin
  dynamic_cursor.openCursor;
end;
/

Links