Re: How do I close cursors?

Thomas,

I am having the same problem with open cursors not being released. We have a system that was suppose to go into production this week for the Department of Defense, but has been postponed due this problem. In our application code, we are issuing a Statement.close call for each SQL statement executed, even if it gets an exception. Some of the discussion threads at Deja.com have mentioned also closing the Result Set, however we do not do this. do the result sets also need to be closed? Is there a bug with the Oracle JDBC driver?

In article <3772c4ff.174361168_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to tasos_at_csa.bu.edu (Anastasios Kotsikonas)
> (if that email address didn't require changing)
> On 23 Jun 1999 00:45:29 GMT, you wrote:
>
> >
> >Hi all,
> >
> >another newbie question...
> >
> >I get this exception from my Java app connecting to 8i via JDBC:
> >
> >Internal Error:
> >java.sql.SQLException: ORA-01000: maximum open cursors exceeded
> >
> > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181)
> > at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:127)
> > at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:350)
> > at

oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:129)
> > at

oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.jav a:128)
> > at lwdb.LWList.GetByName(LWList.java:1944)
> > at lwd.List.getList(List.java:436)
> > at lwd.action.addUser.Begin(addUser.java:154)
> > at lwd.dispatch.WorkerThread.run(WorkerThread.java:246)
> >java.lang.Exception: Caught in:
> > at lwd.cos.email.error.RuntimeError.run(RuntimeError.java:86)
> > at

lwd.cos.email.error.RuntimeError.<init>(RuntimeError.java:62)
> > at lwd.action.addUser.Begin(addUser.java:223)
> > at lwd.dispatch.WorkerThread.run(WorkerThread.java:246)
> >
> >what's causing this?
> >
> >thanks
> >
> >tasos
>
> Is it possible that you are calling lots of routines that each declare
their own
> "Statement" variables and let these go out of scope without calling
.close()?
>
> For example, i think I am able to replicate your issue with the
following code.
>
> I have a table T ( x number );
> my open_cursors is set to 1,000
>
> the following code works:
>
> import java.sql.*;
> import oracle.jdbc.driver.*;
>
> class test {
> public static void main (String args []) throws SQLException {
>
> // Load Oracle driver
> DriverManager.registerDriver (new
oracle.jdbc.driver.OracleDriver());
>
> // Connect to the local database
> Connection conn =
> //DriverManager.getConnection

("jdbc:oracle:thin:@slackdog:1521:oracle8",
> DriverManager.getConnection ("jdbc:oracle:oci8:@slackdog.world",
> "scott", "tiger");
>
> // Query the employee names
> Statement stmt = conn.createStatement ();
>
> stmt.execute( "delete from t" );
>
> for( int i = 0; i <= 2000; i++)
> {
> Statement NewStmt = conn.createStatement ();
>
> NewStmt.execute( "INSERT INTO T VALUES (" + i + ")" );
> NewStmt.close();
> }
> }
> }
>
> **but** if you comment out the NewStmt.close(); inside the loop, it
fails with
> max open cursors after a while. That is because the statement is just
going out
> of scope inside the loop for each iteration -- and we have not closed
the
> cursor. As there are no destructors in java, we can't clean up cursors
> automagically -- java will clean up unused memory but it won't clean
up
> 'everything' possible.
>
> Are you ever closing statements?
>
> --
> 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 Tue Jun 29 1999 - 17:32:16 CDT