This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes java.sql.SQLException: ORA-01000: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: ORA-01000: maximum open cursors exceeded" Watch "java.sql.SQLException: ORA-01000: maximum open cursors exceeded" New topic
Author

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

Sunny Jain
Ranch Hand

Joined: Jul 23, 2007
Posts: 433

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.


Thanks and Regards,
SCJP 1.5 (90%), SCWCD 1.5 (85%), The Jovial Java, java.util.concurrent tutorial
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

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.


There will always be people who are ahead of the curve, and people who are behind the curve. But knowledge moves the curve. --Bill James
Sunny Jain
Ranch Hand

Joined: Jul 23, 2007
Posts: 433

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 ?
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

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 ]
    Sunny Jain
    Ranch Hand

    Joined: Jul 23, 2007
    Posts: 433

    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 ?
    Stevi Deter
    Ranch Hand

    Joined: Mar 22, 2008
    Posts: 265

    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.
    Rob Spoor
    Sheriff

    Joined: Oct 27, 2005
    Posts: 19649
        
      18

    Moving to JDBC as suggested


    SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
    How To Ask Questions How To Answer Questions
    Sunny Jain
    Ranch Hand

    Joined: Jul 23, 2007
    Posts: 433

    any one has any other suggestion ?
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
     
    Similar Threads
    ORA-01000: maximum open cursors exceeded
    ORA-01000 maximum open cursors exceeded
    hi,what cause this Exception?
    Error message : ORA-01000: Maximum number of open cursors reached ??
    Any way to over come "ORA-01000: maximum open cursors exceeded" ??