CREATE OR REPLACE  PACKAGE aepuser.pkg_test
AS
   TYPE cur1 IS REF CURSOR;

   PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1);
END;

CREATE OR REPLACE PACKAGE BODY aepuser.pkg_test
AS
   PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1)
   IS
      v_cur   cur1;
   BEGIN
      OPEN v_cur FOR
         SELECT ename, deptno, sal, comm
           FROM scott.emp;

      io_cur := v_cur;
      COMMIT;
   END get_empdetails;
END;