How to overcome maximum open cursors error

Discussions

EJB programming & troubleshooting: How to overcome maximum open cursors error

  1. How to overcome maximum open cursors error (6 messages)

    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 ?

    Threaded Messages (6)

  2. 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
  3. close all the resources[ Go to top ]

    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
  4. how can we use the resultset?[ Go to top ]

    Hi,

     How can we use the result set if we are closing after every Iteration? It will give exception.
  5. re: how can we use the resultset?[ Go to top ]

    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 ...
  6. Close all the resources[ Go to top ]

    Dont reuse connection, preparestatement and resultset ....
  7. chitu[ Go to top ]

    this may occur in recursive calling of the code.

     

    just increase your cursor limit in the databese configurationj file.