Retreiving rows from a table which is returned by a function.

create or replace package GetNextSet1 as

   type sName is  table of student.stuname%type  index by binary_integer;
   
   type srno is   table of student.rno%type   index by binary_integer;

   type t_objtab is table of student%rowtype index by binary_integer;
   
   procedure CloseCursor;
   
   procedure OpenCursor;

   procedure SetRows (pRows pls_integer);

   function RetrieveRows (pRows   pls_integer := null) return t_objtab;
   
end GetNextSet1;
/


-----------------------------------------------------------------------------------------------------


create or replace package body GetNextSet1 as
   
   vName sName;
   vrno  srno;
   vTable t_objtab;
   
   vRowsDefault   pls_integer := 3;
   vRows          pls_integer;
  
       
   cursor csrEmployees is
   select rno,stuname
   from student;
      
   /* Display retrieved rows */
   procedure ShowBatch
   is
   begin
   
      if (vName.count > 0)
      then
      
         for nIndex in 1..vName.count
         loop
            
            dbms_output.put_line(rpad(vName(nIndex), 20) ||
                                 lpad(to_char(vrno(nIndex)), 5));
            
         end loop;
             
      end if;
   
   end ShowBatch;
   
   /* Reset variables and collections */
   procedure Reset
   is
   begin
   
      vName.delete;
              
      vRows   := vRowsDefault;
   
   end Reset;
   
   /* Close cursor */
   procedure CloseCursor
   is
   begin
      
      if csrEmployees%isopen
      then
         close csrEmployees;
         
         Reset;
      end if;
      
   end CloseCursor;
   
   /* Open cursor */
   procedure OpenCursor
   is
   begin
   
      CloseCursor;      
         
      open csrEmployees;

   end OpenCursor;

   /* Retrieve rows from the cursor in chunks of pRows.
      If the cursor is not open, open it for the salary
      value of pSalary
   */
   function RetrieveRows (pRows   pls_integer := null) return t_objtab
   is
   begin
   
     if not csrEmployees%isopen
     then
             
   dbms_output.put_line('Opening the cursor.');
        OpenCursor;
   end if;
   
   if (nvl(pRows, 0) > 0)
        then
           vRows := pRows;
        else
           vRows := vRowsDefault;
        end if;
     
     fetch csrEmployees bulk collect into vTable limit vRows;
     
     --ShowBatch;

     
     if csrEmployees%notfound
     then
     
        dbms_output.put_line('All rows retrieved. Closing the cursor.');
        CloseCursor;
     
     end if;

     return vTable;
        
   end RetrieveRows;

    /* Set the default value of rows */
   procedure SetRows (pRows pls_integer)
   is
   begin
      
      if (nvl(pRows, 0) > 0)
      then
         
         vRowsDefault := pRows;
         
      end if;
      
   end SetRows;
   
begin
   
   /* Initialization */
   vRows   := vRowsDefault;
   
end GetNextSet1;
/



---------
Executing
---------

declare t getnextset1.t_objtab;
begin
   t :=getnextset1.RetrieveRows();
end;