Thread: Challenging problem ... How to get the ResultSet from an Oracle stored procedure

Challenging problem ... How to get the ResultSet from an Oracle stored procedure

From
"Panchumarthy, Mallikarjuna rao (CTS)"
Date:
Hi,
I have the following package in the Oracle 8i database and I want to get the
data in a java program. I don't know how to get it. I tried to
registerOutParameter with different permutations and combinations but no use
.. it says that " data type is unresolved."

can u  help me please...........

PACKAGE CLAIM_PKG
AS

TYPE TBLCLAIMED       IS TABLE OF VARCHAR2(1)    INDEX BY BINARY_INTEGER;
TYPE TBLPERIOD        IS TABLE OF NUMBER(6)      INDEX BY BINARY_INTEGER;
TYPE TBLCLAYEAR       IS TABLE OF VARCHAR2(4)    INDEX BY BINARY_INTEGER;

PROCEDURE SP_GET_LTA_DETAILS (
ORGID VARCHAR2,
EMPID VARCHAR2,
EXCLAIMED OUT  TBLCLAIMED,
PERIODOFTRA OUT TBLPERIOD,
CLAYEAR  OUT TBLCLAYEAR);

END claim_pkg;





PROCEDURE SP_GET_LTA_DETAILS (
ORGID VARCHAR2,
EMPID VARCHAR2,
EXCLAIMED OUT  TBLCLAIMED,
PERIODOFTRA OUT TBLPERIOD,
CLAYEAR  OUT TBLCLAYEAR)

IS
 CURSOR LTADETAIL(SYEAR NUMBER) IS
 SELECT A.EXEMPTION_CLAIMED EC,  A.PERIOD_OF_TRAVEL PT, A.CALENDAR_YEAR CY FROM T_EMPLTA_PREV_DETAILS A,
T_EMPLTA_MASTERB WHERE (A.SERIAL_NUM = B.SERIAL_NUM) AND (LOWER(B.ORG_ID) = LOWER(ORGID))
 
AND (LOWER(A.EMP_ID) = LOWER(EMPID) ) AND (TO_NUMBER(A.CALENDAR_YEAR) > SYEAR);
 CURYEAR NUMBER(4); X NUMBER(3); RECCOUNT NUMBER DEFAULT 0; STARTYEAR NUMBER(4);


BEGIN CURYEAR :=(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))); X := MOD((CURYEAR- 2001), 4); IF X = 0 THEN   X := 4; END IF;
STARTYEAR:= CURYEAR-X;
 
 FOR LTADETAILREC IN LTADETAIL(STARTYEAR) LOOP
   RECCOUNT := RECCOUNT + 1;
   EXCLAIMED (RECCOUNT) := LTADETAILREC.EC;   PERIODOFTRA (RECCOUNT) := LTADETAILREC.PT;   CLAYEAR (RECCOUNT) :=
LTADETAILREC.CY;
 END LOOP;


END;

END CLAIM_PKG;









This e-mail and any files transmitted with it are for the sole use 
of the intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient, please contact the sender by reply e-mail and 
destroy all copies of the original message. Any unauthorised review, use, disclosure, 
dissemination, forwarding, printing or copying of this email or any action taken in 
reliance on this e-mail is strictly prohibited and may be unlawful...
    Visit us at http://www.cognizant.com