REF CURSOR

CREATE OR REPLACE PACKAGE climb_biz
AS
TYPE ref_cur IS REF CURSOR return students%ROWTYPE;
PROCEDURE run(p1 in number);
PROCEDURE test_cur(mycur OUT ref_cur);
END climb_biz;

CREATE OR REPLACE PACKAGE BODY climb_biz IS
  
  -- Define procedure.
  PROCEDURE run(p1 NUMBER) IS
    mycur ref_cur ;
    r_temp students%ROWTYPE;
  BEGIN
    BEGIN 
      test_cur(mycur);

      LOOP FETCH mycur INTO r_temp;
        EXIT WHEN mycur%NOTFOUND; 
        dbms_output.put_line(r_temp.name);
      END LOOP;  

    END;
    
  END run;
  
  PROCEDURE test_cur(mycur OUT ref_cur) IS
   
  BEGIN
    
     OPEN mycur FOR SELECT * FROM students;
  END test_cur;
END climb_biz;