Reference Cursors and Java ?

From: Lizz Pena <lizz_pena_at_yahoo.com>
Date: Fri, 01 Nov 2002 10:38:56 -0800
Message-ID: <F001.004FA0D6.20021101103856@fatcity.com>

I have some questions with reference cursors: I'm using Oracle 8.1.7 (on windows 2000:( )now but would be interested in both 8.1.7 and Oracle 9i.

1>Is is possible to return a ref cursor to a Java program (using JDBC API)?
2> How efficient is it as opposed to using preparestatement?
3> Are there any other efficient ways of doing this either on Java side or by using stored procedures. 4>Any white papers that discuss this?

Here is the package I have now. Thanks in advanced. -Lizz

CREATE OR REPLACE PACKAGE PKG_PENDING_REPORT AS TYPE RC_RETURN IS REF CURSOR; PROCEDURE SP_INCOMPLETE_CALLS_BY_ACCT
(V_DAYS IN NUMBER,RT_CALLS OUT

PKG_PENDING_REPORT.RC_RETURN);
END PKG_PENDING_REPORT;
/

CREATE OR REPLACE PACKAGE BODY PKG_PENDING_REPORT AS PROCEDURE SP_INCOMPLETE_CALLS_BY_ACCT
(V_DAYS IN NUMBER, RT_CALLS OUT

PKG_PENDING_REPORT.RC_RETURN) IS
BEGIN
OPEN RT_CALLS FOR
SELECT ACCOUNT_NO
FROM CDR_TABLE
WHERE CDR_START_DT >SYSDATE - V_DAYS
AND CDR_FLAGS=0 ;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN RAISE;
END SP_INCOMPLETE_CALLS_BY_ACCT ;
END PKG_PENDING_REPORT;
/

---

Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lizz Pena
  INET: lizz_pena_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Nov 01 2002 - 12:38:56 CST