Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


pgsql-interfaces by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group