Help!: Oracle problem with Cursors and Stored Procedures

We're having problems using stored procedures in Oracle with JDBC, because it looks like returned cursors aren't getting closed, and we're not sure how to force the cursors to close properly.

Below is the detailed description from my colleague regarding this problem (I'm posting the question because I have access to the new group).

Any answer emailed directly to me (jeffb_at_halcyon.com) or my colleague (bab_at_teamdci.com) will be repost for everyone else to learn from. Thanks.

Here's my colleague's question:

It appears that the way result sets are returned in Oracle stored procedures
is by declaring a cursor:

 CREATE OR REPLACE PROCEDURE ep_get_foo(

    oReturnCursor OUT REFCURSOR,
    iID IN INTEGER)
 AS
 BEGIN
   OPEN
     oReturnCursor
   FOR
   SELECT Name
   FROM NameTab
   WHERE ID = iID;
 END;
/

This works by calling it via a JDBC CallableStatement object:  CallableStatement cs =
   conn.prepareCall("{ call ep_get_foo(?, ?) }");  cs.registerOutParameter(1, OracleTypes.CURSOR);  cs.execute();
 ResultSet rs = (ResultSet)cs.getObject(1);

Not only does this seem a bit wacky to me (why can't I get the result set from the execute() call just like I can if I'm calling SQL directly?) but it
also leaves an open cursor hanging about. I've tried closing the CallableStatement object after using the result set but I eventually use up
all the available cursors so that isn't working. HELP! There must be a way to either:

1. Create the cursor explicitly and pass it to the SP
2. Find another way to close the cursor
3. Avoid this cruft and do it the Java way.

Any explanations/help? Received on Mon Apr 26 1999 - 17:47:01 CDT