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

From: "Panchumarthy, Mallikarjuna rao (CTS)" <PMallika(at)BLR(dot)COGNIZANT(dot)COM>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Challenging problem ... How to get the ResultSet from an Oracle stored procedure
Date: 2001-03-28 11:32:33
Message-ID: ED5E2A9386A4D411BCC900508BF7E43D201A31@CTSINBLRSXUA
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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;

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

Browse pgsql-interfaces by date

  From Date Subject
Next Message Joel Burton 2001-03-28 14:51:13 Re: Mini Faq
Previous Message Takehiko Abe 2001-03-28 10:20:54 Re: Libpq for the MacOS (2)