ORA-01000: maximum open cursors exceeded - java code fails

Question

I use loops in my java code to insert rows into my database. Then, I got the error -

ORA-01000: maximum open cursors exceeded

I searched a little and saw that closing my prepared statements or statements after adding each row helps to get rid of the error.

But, I need to understand -

1. what are cursors ?
2. Can i change the number of cursors ? If yes, is it a good thing to do ?

Answer 1

This link will will explain you what a cursor is and how it works.

Yes you can change the maximum number of cursors on Oracle using the following statement: ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;

But you should do that just if really needed. What you really should do is handle resultset and statements correctly, and ensure that you always close them. This should typically be done in a try/finally statement.

If you forget to close these, open cursors will leak until you reach the maximum limit (which comes very quickly), and subsequent requests won't work (this is the case for you).

You could edit your question and add some code so that we can show you some hint about how and where your may close the resultset and statement properly.

This is typical usage:

Statement stmt;
try {
   stmt = con.createStatement();
   // do something with the statement
} catch (SQLException e) {
   LOG.error("SQL error", e);
} finally {
   try { if (stmt != null) stmt.close(); } catch (SQLException se) { LOG.error("Error closing the statement", se); }
   // if using Apache utils you could do
   // IOUtils.closeQuietly(stmt);
}

It is the same with resultset. Depending on the version of Java you're using you could use the try-with-resources idiom.

try (Statement stmt = con.createStatement()) {
   // do something with the statement
} catch(SQLException e) {
   LOG.error("SQL error", e);
}

Java will take care of closing the statement at the end of the try block, since Statement implements the AutoCloseable interface.

Answer 2

This should be dealt from the application only.It has nothing to do with database.Some of statements are NOT CLOSED properly.Even if open_cursors=10000, this error will occur.Before opening every statement, check whether it is already left open or not.Similarly try to close every statement.See this link to more details.

Answer 3

When you run the method executeQuery of statement, you are creating a cursor. Cursor is defined in database, and it can be controlled by jdbc. You can find more information from the follow url. wiki for cursor