Re: Fatal bug in Oracle JDBC OCI driver - Oracle admits it.

[Quoted] A copy of this was sent to "Dean Willis" <dean_at_logical-approach.com> (if that email address didn't require changing) On Sat, 22 May 1999 18:22:23 GMT, you wrote:

>I've just spent two days checking everything in my system, and multiple
>calls to oracle technical support to find out that there is a problem in the
>Oracle JDBC OCI driver that Oracle thinks may or may not be a problem worth
>fixing. The driver builds a cursor when it does an insert and then fails to
>release that cursor. In addition to being a waste of resources, when you
>reach the number of inserts that is equal to your ORACLE MAX_CURSORS, you
>will abend with an ORA-01000 Max open cursors exceeded. The only workaround
>for this is to keep track of how many inserts you have made, and before it
>gets to the MAX_CURSORS limit, then you must disconnect and re-connect to
>the database. Keep in mind that Oracle and the drivers open cursors that
>you don't know about, so you are never really sure when you need to
>disconnect and reconnect to the database. Oracle has asked me to send them
>all my code, and the abend and everything else they could think of, because
>even though they know about this bug, they are not sure it is serious enough
>to fix. If you are only doing a few inserts, then it will only waste your
>resources, but if you are doing a real application that does many inserts,
>it will abend on you. I can't believe that Oracle is so arrogant about this
>to think it not a problem that needs fixing.
>
>These are the JDBC OCI drivers on the Oracle CD for Oracle 8.0.5, so I do
>not know if it affects other versions, but I would suspect so. We are also
>running Solaris 2.7 with JDK 1.1.
>

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:_at_slackdog:1521:oracle8",
      DriverManager.getConnection ("jdbc:oracle:oci8:_at_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?

still be interested in getting the tar number in any case

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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