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

Thank you very much for the help!
I my test program, I missed this part:
> ResultSet rset = (ResultSet)cstmt.getObject(1);
> rset.close();

So the some cursor variables were not closed. I thought if I close the CallableStatement, the ResultSet will be closed automatically. If this is not closed, ant the certain amount of memory is used by PLSQL table, plus JDBC, Oracle will generate a core dump. If less memory is taken by PLSQL table, Oracle will give a out of opened cursor error. I started from the core dump, there was a long way before I narrow down to this point.
The lesson I learned: if cursor variable is used with JDBC, even you do not
need anything from the cursor variable, get it and close it.

Thanks.
--
Hang Chen, Software Engineer
Time0 Group, Perot Systems
412-4426981

• Original Message ----- From: Thomas Kyte <tkyte_at_us.oracle.com> Newsgroups: comp.databases.oracle.misc To: <hchen_at_time-0.com> Sent: Wednesday, August 11, 1999 8:09 AM Subject: 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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 18 1999 - 09:21:36 CDT