Feb 11, 2016

Examples of Ref Cursor1

REF CURSOR Example


create or replace package strongly_typed is
    type return_cur is ref cursor return all_tables%rowtype;
    procedure child(p_return_rec out return_cur);
    procedure parent(p_numrecs pls_integer);
end strongly_typed;

-----------------------
create or replace package body strongly_typed is

procedure child(p_return_rec out return_cur) is
begin
   open p_return_rec for select * from all_tables;
end child;

procedure parent (p_numrecs pls_integer) is
p_retcur return_cur;

at_rec all_tables%rowtype;
begin
   child(p_retcur);
   for i in 1 .. p_numrecs loop fetch p_retcur into at_rec;
   dbms_output.put_line(at_rec.table_name || ' - ' || at_rec.tablespace_name || ' - ' || to_char(at_rec.initial_extent) || ' - ' || to_char(at_rec.next_extent));
   end loop;
end parent;

end strongly_typed;

No comments: