Re: How to close cursor variable from JDBC w/o close connection?

A copy of this was sent to hchen_at_time-0.com (if that email address didn't require changing) On Wed, 11 Aug 1999 00:20:35 GMT, you wrote:

>Hi,
>Currently we experiencing a problem:
>The PLSQL package returns JDBC a cursor variable. Java program
>can retrieve record from the cursor with no problem.
>The problem is: how to close this cursor variable?
>
>We are using a connection pool, so can NOT close the connection.
> After about 50 calls to this plsql function, I got error of "max
>open cursors exceed". I tried CallableStatement.close(), but
>seems like it does nothing. This is a serious problem for us, in
>an older version, it even result in core dump (big PLSQL table,
>plus cursor variable, plus max cursor variable exceed).
>Our environment is Oracle 7.3, JDK1.1. ( I do found there is a
>new function in JDK1.2, getRef and setRef, can I pass the Ref of
>a cursor back to a plsql function to close it?)
>

resultset.close and callablestatement.close are all that it takes.

Consider this small plsql snippet:

create or replace package types
as

        type refcur is ref cursor;
end;
/

create or replace procedure get_enames( x in out types.refcur ) is
begin

        open x for select ename from emp; end;
/

Now, run a java program like:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
  public static void main (String args [])

                     throws SQLException, ClassNotFoundException
  {
      String driver_class = "oracle.jdbc.driver.OracleDriver";
      String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";
      String query  = "begin curvar_demo.get_enames( :1 ); end;";
      Connection conn;
      int         rsetCount;

      Class.forName(driver_class);
      conn = DriverManager.getConnection(connect_string, "scott", "tiger");

      Statement stmt = conn.createStatement ();

      for ( int i = 0; ; i++ )
      {
          CallableStatement cstmt = conn.prepareCall(query);
          cstmt.registerOutParameter(1,OracleTypes.CURSOR);
          cstmt.execute();

          ResultSet rset = (ResultSet)cstmt.getObject(1);

          for (rsetCount = 0; rset.next (); rsetCount++ );

          System.out.println( "Iteration " + i +
                              " Fetched " + rsetCount + " rows..." );
   
          rset.close();
          cstmt.close();

  }
}

I have open_cursors set to 50 and if you comment out the cstmt.close(), it bombs at iteration 50. put it back in and it runs forever.

You must be missing the cstmt.close() in some or all cases.

>Any hint would be greatly appreciated!
>
>Hang Chen
>hchen_at_time.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 11 1999 - 07:09:45 CDT