aspose file tools*
The moose likes JDBC and the fly likes ORA-01000: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01000: maximum open cursors exceeded" Watch "ORA-01000: maximum open cursors exceeded" New topic
Author

ORA-01000: maximum open cursors exceeded

Pat Garner
Greenhorn

Joined: Mar 05, 2004
Posts: 9
I've looked about the web for the answer to this problem but I haven't gotten a clear understanding of the problem. Neither have I discovered a practical way to fix it. I'm pasting pseudo code below, displayed in a tree-like structure so it's clear how the Connection object is being passed around. Hopefully it will be clear by looking at the pseudo code that no ResultSet nor PreparedStatement is being passed around. All ResultSets and PreparedStatements are created locally using the passed-around Connection and they are then closed locally in a finally block.
So let there be no mistake: I am closing all ResultSet and PreparedStatement objects locally. Each method that the Connection object instance gets passed into closes the ResultSet object instance and the PreparedStatement object instance within a finally block.
PopulateStudent
---------------
|
|--- Class.forName(oracleDriver);
|
|--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
|
|--- ps = con.prepareStatement(sb.toString());
|
|--- rs = ps.executeQuery();
|
|--- while(rs.hasNext())
| -----
| |
| |--- stu = Student.getInstance(rs.getString(),rs.getDate(), con);
| | -------
| | |
| | |---- Sequence.STUDENT.getNextId(con);
| | --------
| | |
| | |---- ps = con.prepareStatement(SELECT);
| | |
| | |---- rs = ps.executeQuery();
| | |
| | |---- if(sequence not initialized)
| | | {
| | | ps = con.prepareStatement(INIT);
| | | ps.executeUpdate();
| | | ps = con.prepareStatement(SELECT);
| | | rs = ps.executeQuery();
| | | }
| | |
| | |
| | |---- ps = con.prepareStatement(INCREMENT);
| | |
| | |---- ps.executeUpdate();
| | |
| | |---- rs.close();
| | |
| | |---- ps.close();
| |
| |--- stu.insert(con);
| -------
| |
| |---- ps = con.prepareStatement(insert.toString());
| |
| |---- for each field in the student object instance...
| | {
| | ps.setXXX(fieldIndex,fieldValue);
| | }
| |
| |---- ps.executeUpdate();
| |
| |---- ps.close();
|
|---- rs.close();
|
|---- ps.close();
|
|---- con.close();

If it's not obvious from the above diagram how the code is written and executed I'll be happy to post the code itself.
Here's the problem:
1. I get an ORA-01000: maximum open cursors exceeded after only 296 rows of student data are inserted into the student table using the Student.insert() method.
2. The problem goes away if, after every couple hundred rows have been inserted, inside the while(rs.hasNext()) I set con=null and then reinitialize con.
So it appears that the problem is the Connection object. Although rs.close() and ps.close() are properly called the Connection object does not close the cursors. This is either a big nasty bug or I'm doing something _way_ wrong. If it's a bug, this is something that is huge and should be well documented. Where's the workaround and/or bug fix? I'm using the oracle thin driver. As an aside, does anybody know for sure whether this is a driver bug or an Oracle DBMS bug?
I absolutely cannot reset the connection every couple hundred rows because I lose the ability to rollback all previously inserted rows if an exception gets thrown.
So what can I do? Any ideas would be much appreciated.
Thanks,
Pat
Pat Garner
Greenhorn

Joined: Mar 05, 2004
Posts: 9
PopulateStudent
---------------
  |
  |--- Class.forName(oracleDriver);
  |
  |--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
  |
  |--- ps = con.prepareStatement(sb.toString());
  |
  |--- rs = ps.executeQuery();
  |
  |--- while(rs.hasNext())
  |    -----
  |      |
  |      |--- student = Student.getInstance(rs.getString(), rs.getDate(), con);
  |      |    -------
  |      |      |
  |      |      |----  Sequence.STUDENT.getNextId(con);
  |      |          --------
  |      |            |
  |      |            |----  ps = con.prepareStatement(SELECT);
  |      |            |
  |      |            |----  rs = ps.executeQuery();
  |      |            |
  |      |            |----  if(sequence not initialized)
  |      |            |      {
  |      |            |          ps = con.prepareStatement(INIT);
  |      |            |          ps.executeUpdate();
  |      |            |          ps = con.prepareStatement(SELECT);
  |      |            |          rs = ps.executeQuery();
  |      |            |      }
  |      |            |
  |      |            |
  |      |            |----  ps = con.prepareStatement(INCREMENT);
  |      |            |
  |      |            |----  ps.executeUpdate();
  |      |            |
  |      |            |----  rs.close();
  |      |            |
  |      |            |----  ps.close();
  |      |  
  |      |--- student.insert(con);
  |           -------
  |             |
  |             |----  ps = con.prepareStatement(insert.toString());
  |             |
  |             |----  for each field in the student object instance...
  |             |      {
  |             |          ps.setXXX(fieldIndex,fieldValue);
  |             |      }
  |             |
  |             |----  ps.executeUpdate();
  |             |
  |             |----  ps.close();
  |
  |----  rs.close();
  |
  |----  ps.close();
  |
  |----  con.close();
Pat Garner
Greenhorn

Joined: Mar 05, 2004
Posts: 9
Originally posted by Pat Garner:
I've looked about the web for the answer to this problem but I haven't gotten a clear understanding of the problem. Neither have I discovered a practical way to fix it. I'm pasting pseudo code below, displayed in a tree-like structure so it's clear how the Connection object is being passed around. Hopefully it will be clear by looking at the pseudo code that no ResultSet nor PreparedStatement is being passed around. All ResultSets and PreparedStatements are created locally using the passed-around Connection and they are then closed locally in a finally block.
So let there be no mistake: I am closing all ResultSet and PreparedStatement objects locally. Each method that the Connection object instance gets passed into closes the ResultSet object instance and the PreparedStatement object instance within a finally block.
PopulateStudent
---------------
  |
  |--- Class.forName(oracleDriver);
  |
  |--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
  |
  |--- ps = con.prepareStatement(sb.toString());
  |
  |--- rs = ps.executeQuery();
  |
  |--- while(rs.hasNext())
  |    -----
  |      |
  |      |--- student = Student.getInstance(rs.getString(), rs.getDate(), con);
  |      |    -------
  |      |      |
  |      |      |----  Sequence.STUDENT.getNextId(con);
  |      |          --------
  |      |            |
  |      |            |----  ps = con.prepareStatement(SELECT);
  |      |            |
  |      |            |----  rs = ps.executeQuery();
  |      |            |
  |      |            |----  if(sequence not initialized)
  |      |            |      {
  |      |            |          ps = con.prepareStatement(INIT);
  |      |            |          ps.executeUpdate();
  |      |            |          ps = con.prepareStatement(SELECT);
  |      |            |          rs = ps.executeQuery();
  |      |            |      }
  |      |            |
  |      |            |
  |      |            |----  ps = con.prepareStatement(INCREMENT);
  |      |            |
  |      |            |----  ps.executeUpdate();
  |      |            |
  |      |            |----  rs.close();
  |      |            |
  |      |            |----  ps.close();
  |      |  
  |      |--- student.insert(con);
  |           -------
  |             |
  |             |----  ps = con.prepareStatement(insert.toString());
  |             |
  |             |----  for each field in the student object instance...
  |             |      {
  |             |          ps.setXXX(fieldIndex,fieldValue);
  |             |      }
  |             |
  |             |----  ps.executeUpdate();
  |             |
  |             |----  ps.close();
  |
  |----  rs.close();
  |
  |----  ps.close();
  |
  |----  con.close();

If it's not obvious from the above diagram how the code is written and executed I'll be happy to post the code itself.
Here's the problem:
1. I get an ORA-01000: maximum open cursors exceeded after only 296 rows of student data are inserted into the student table using the Student.insert() method.
2. The problem goes away if, after every couple hundred rows have been inserted, inside the while(rs.hasNext()) I set con=null and then reinitialize con.
So it appears that the problem is the Connection object. Although rs.close() and ps.close() are properly called the Connection object does not close the cursors. This is either a big nasty bug or I'm doing something _way_ wrong. If it's a bug, this is something that is huge and should be well documented. Where's the workaround and/or bug fix? I'm using the oracle thin driver. As an aside, does anybody know for sure whether this is a driver bug or an Oracle DBMS bug?
I absolutely cannot reset the connection every couple hundred rows because I lose the ability to rollback all previously inserted rows if an exception gets thrown.
So what can I do? Any ideas would be much appreciated.
Thanks,
Pat
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
It's a little hard to tell what's going on w/out seeing the actual code. But you might want to double check to see if there is any method that is using the same "rs" twice but only closing it once. For example, looking at your pseudo-code for the "Student.getInstance()" method (?) I see the following:

As I said, it's hard to see what's REALLY going on w/out the actual code, but my guess is that you're using the "rs" variable to hold on to different ResultSet instances w/out closing previous ones.
If this is not it, please supply the source code.
Pat Garner
Greenhorn

Joined: Mar 05, 2004
Posts: 9
Yessirree Wayne you surely were right! I was reusing the ps (PreparedStatement) and rs (ResultSet) variables multiple times without calling close() in between, and this is what caused the database cursor leak. Thanks for your help!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-01000: maximum open cursors exceeded