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;

Examples of Ref Cursor

SYS_REFCURSOR  Example
create or replace function f return SYS_REFCURSOR as
  c SYS_REFCURSOR;
begin
  open c for select empno  from employees;
  return c;
end;
----------------------------------------------------

declare
  refCursorValue  SYS_REFCURSOR;
  v varchar2(1);
begin
 refCursorValue := f();   -- Get ref cursor from function
 loop
   fetch refCursorValue into v;
   exit when refCursorValue%notfound;
   dbms_output.put_line('Value from cursor: '||v);
 end loop;
END;
----------------------XXXXXXXXXXXXXXXXXXXXXXXXXXX-------------

  declare
      refCursorValue  SYS_REFCURSOR;
      myRecord product%rowtype;
  begin
      open refCursorValue for select * from product ;
      loop
        fetch refCursorValue into myRecord;
        exit when refCursorValue%notfound;
     end loop;
     closerefCursorValue;
  end;