Stored Procedure using Ref cursor --01

Normal Table:


CREATE TABLE TEST_TICKET
( SR_NO VARCHAR2(240),
NET_TIME VARCHAR2(240),
TOTAL_TIME VARCHAR2(240),
LAST_NAME VARCHAR2(240),
USERID VARCHAR2(240),
LOCATION VARCHAR2(240),
CALLTYPE VARCHAR2(240),
STATUS VARCHAR2(240),
PRIORITY VARCHAR2(240),
CREATEDDATE VARCHAR2(240),
STATUSDATE VARCHAR2(240),
CREATEDBY VARCHAR2(240)
)




Droping a table :

drop table TEST_TICKET

select * from TEST_TICKET

truncate table test_ticket.



Package specification...

CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;




Procedure to display output:


CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;