Challenging problem ... How to get the ResultSet from an Oracle stored procedure
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Thursday, March 29, 2001 11:52 PM

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 TB   More>>

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_MASTER B
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;

   <<Less
About | Sitemap | Contact