USE OF REF-CURSOR IN THE PACKAGE
================================
PACKAGE
-----------------
-
create or replace package pack1
-
as
-
type mycur is ref cursor;
-
end;
Note :--since the package does not contain any procedure or functions it not required to create the package body.
----------------------------
USING THE (GLOBAL) REFCURSOR IN A PROCEDURE
---------------------------------------------------------------------
- create or replace procedure testproc
-
(
-
no emp.deptno%type,
-
rec out pack1.mycur
-
)
-
is
-
begin
-
open rec for select * from emp where deptno=no;
-
end ;
--------------------------------
CALLING THE PROCEDURE FROM ANNONYMOUS BLOCK
==============================================
- declare
-
mm pack1.mycur;
-
ll emp%rowtype;
-
begin
-
testproc(10,mm);
-
loop
-
fetch mm into ll ;
-
exit when mm%notfound;
-
DBMS_OUTPUT.PUT_LINE(ll.empno||' ' ||ll.ename);
-
end loop;
-
end;
FUNCTION OVERLOADING IN PACKAGE.
=================================
package
----------------
-
CREATE OR REPLACE PACKAGE PKG_OVRLD
-
AS
-
FUNCTION TRANS_DATE(DT IN DATE)RETURN NUMBER;
-
FUNCTION TRANS_DATE(DT IN NUMBER)RETURN DATE;
-
END PKG_OVRLD;
package body
------------------------
-
CREATE OR REPLACE PACKAGE BODY PKG_OVRLD AS
-
-
FUNCTION TRANS_DATE(DT IN DATE)RETURN NUMBER IS
-
BEGIN
-
RETURN ROUND((DT-TO_DATE('01011970','MMDDYYYY'))*86400);
-
END TRANS_DATE;
-
-
FUNCTION TRANS_DATE(DT IN NUMBER)RETURN DATE IS
-
BEGIN
-
RETURN (TO_DATE('01011970','MMDDYYYY') +(DT/(86400)));
-
END TRANS_DATE;
-
-
END PKG_OVRLD;
-
Also check
PACKAGE - 4 
