java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Hi All,

I am getting following error, while executing one service :
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

After googling, I found that this exception occur when too many result set , prepared statements are left open. After that when i saw the code, i found that not even a single result set was closed.

Is there any way that can tell me upto what number the resultset Objects are allowed in Java.

Sunny,

The number of cursors permitted by your database is what you're concerned with here. JDBC will merrily permit you to open cursors until the database balks.

What you need to do here is refactor the code so it closes result sets and connections as it goes to prevent this from happening.

I have checked with DBA, 1000 Cursor are allowed.
While checking I found that if we close the Prepared statement, then there is no need to close the Result Set.

Also we are using the same connection Object, through out the process and we are hitting very huge database. i mean number of insert, update statements are very large.

I am afraid, what if after taking the pain to close the result sets, the problem will not resolved ?

It might be a good time for a bartender to move this topic to the JDBC forum so you can get a wider range of expertise.

It is accurate that closing a Statement will also close the ResultSet.

If you find that properly closing your ResultSet a/o Statement and connections as you finish with them leads you to still continually hit the cursor maximum for your database you can

reexamine your code to find places where you can do more of your work with fewer cursors. Try using batch updates, etc.

work with your DBA to determine if increasing the cursor maximum is possible

I'd highly recommend focusing as much effort as possible on the first, as it will force you to design the JDBC layer in the most efficient way possible.
[ May 30, 2008: Message edited by: Stevi Deter ]

but in order to change the code, i will be needed the permission and i know the first thing that my boss will say me, there is no need to close the result set , as it will be closed automatically.

also, i don't know who did that coding, no try-catch has been used, every method is throwing an exception, after executing the prepared Statement, they have closed the prepared statement. Ideally we should use close method inside finally.

What is the good excuse or reason, I can tell to my boss so that he let me to change the code ?

For all the reasons you just stated!

Are the PreparedStatements being closed? You don't get your automatic closure of the ResultSet without actually closing the PreparedStatements.

If the code isn't following best practices, and it sounds like it's not, fixing it is the Right Thing To Do. Otherwise, you'll just continue to have problems with it.

Moving to JDBC as suggested

any one has any other suggestion ?