How to overcome this error

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

Iam not getting this error frequently. In rare instantce its happening.
Pls do let me know
1) In Which scenrios this error will come
2) How to over come this ?

1. All the resources (including cursors) are released when a connection is closed.
You may need to release unused cursors sooner by closing unused resultsets <I would suggest a code review to fix this problem.
2. If problem still remains that means your application needs more open cursors, ask your DBA to increase the number of open cursors that can be maintained with database your application is connecting to.

-DG

We had the same problem in our application. We were using a connection object and executing queries
n times within a loop and not closing ResultSet/Statement object at the end of each loop iteration
We closed all the ResultSet/Statement object after each iteration and resolved the issue.
You can log-in as admin in your database and you can check number of open cursors throug sql commands
execute the module and find out which part of code is giving problem. If you are using Oracle DB then
you can connect through Toad and get all the details

Hi,

 How can we use the result set if we are closing after every Iteration? It will give exception.

You don't have to close the ResultSet immediately afterwards ... you can do your work and then close the ResultSet. So, don't create a Collection of ResultSet objects. Get one, do your thing, then get the next, etc.

This exception is triggered after a certain number of calls. You could keep a counter and refresh your connection before you go over this max. This is a hack ...

Dont reuse connection, preparestatement and resultset ....

this may occur in recursive calling of the code.

 

just increase your cursor limit in the databese configurationj file.