Re: How do I close cursors?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 23 Jun 1999 11:29:43 GMT
Message-ID: <3772c4ff.174361168@newshost.us.oracle.com>

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.java: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 Received on Wed Jun 23 1999 - 06:29:43 CDT