This page contains an archived post to the Java Answers Forum made prior to February 25, 2002. If you wish to participate in discussions, please visit the new Artima Forums.

Message:

Challenging question for JDBC GURUs "How to get a ResultSet from an Oracle Stored procedure"

Posted by Mallikarjuna Rao.P on March 27, 2001 at 7:44 AM

Hello
I want to get a ResultSet object from an oracle stored procedure and I am lost..

after a prolonged racking of the brain I could come upto this..
1. First I created a package in Oracle it is as follow

CREATE OR REPLACE 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;
/

CREATE OR REPLACE PACKAGE body CLAIM_PKG
AS

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;

/

From this package I am unable to get the data.. how shall I have to registerOutParameter()? What is the data type We need to use to get that data in a java program?