File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Deepak Chopra
Ranch Hand
Posts: 433
Eclipse IDE Firefox Browser Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stevi Deter
Ranch Hand
Posts: 265
Hibernate Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Deepak Chopra
Ranch Hand
Posts: 433
Eclipse IDE Firefox Browser Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
Hibernate Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
     
    Deepak Chopra
    Ranch Hand
    Posts: 433
    Eclipse IDE Firefox Browser Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 265
    Hibernate Java Spring
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Pie
    Posts: 20399
    47
    Chrome Eclipse IDE Java Windows
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Moving to JDBC as suggested
     
    Deepak Chopra
    Ranch Hand
    Posts: 433
    Eclipse IDE Firefox Browser Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    any one has any other suggestion ?
     
    I agree. Here's the link: http://aspose.com/file-tools
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic