It's not a secret anymore!*
The moose likes JDBC and the fly likes 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 Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01000 maximum open cursors exceeded" Watch "ORA-01000 maximum open cursors exceeded" New topic
Author

ORA-01000 maximum open cursors exceeded

Rajesh Veluchamy
Ranch Hand

Joined: Jan 15, 2008
Posts: 47



Thanks in advance


-Rajesh.V<br /> Software Consultant
fred rosenberger
lowercase baba
Bartender

Joined: Oct 02, 2003
Posts: 11164
    
  16

Thanks in advance... for what? You don't seem to be asking a question here. Generally speaking, the best way to get help around here is to ask simple, focused, and direct questions. What 99% of the folks who look at your post are going to do is say "well, I see a hundred lines of code and no indication of what the poster wants, so I'll spend my time elsewhere".

I would suggest you read much of this, but in particular this, this, and this.


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
Rajesh Veluchamy
Ranch Hand

Joined: Jan 15, 2008
Posts: 47
i want to run a for loop for more than 1000 time
and each time the will execute a simple query

but my problem is after 700 time i am getting "ORA-01000 maximum open cursors exceeded" exception

my cursor limit is 700, i can't increase it

what i want to know is
why my cursors are not closed
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

Because you don't close the resultset/statment/connection after each loop - or after each 100th or 200th or XXth (less then the max open cursors, plus a margin if the connection are used to other SQL stuff) loop...

If you don't close both the resultset/statment/connection they count for three open cursors.


Regards, Rene Larsen
Dropbox Invite
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Rajesh,
Also note that you could optimize this whole thing to make it one SQL statement. It's still important to close the resources properly (in a finally block.) This approach is faster and uses less resources though.

Consider if you changed the checkAccountNo method signature to:

This method would return all the account numbers found in the table from the list of candidate ones. This is one SQL query and not a whole bunch of them. Then in Java you can loop through and handle the missing ones.

Or this one:

This method would return the number of matching accounts found in the table from the list of candidate ones. Since the if statement doesn't seem to care which accounts aren't found, this would be even faster because you don't have to send a list of account ids back from the database.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Rajesh Veluchamy
Ranch Hand

Joined: Jan 15, 2008
Posts: 47
thanks Boyarsky & Larsen,

one SQL will work but there is a issue too, in a single oracle "in" statement, we can pass only 1000 iputs. Even that can be solved by assigning in a array or something else.

But i want to know why my cursors are not closed
Even though i have closed all statement, result set and connections

and my cursors are closed when exception occurs
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Rajesh,
You can always do the query in sets of 900 or so. (I didn't realize you had over 1000. If that is the case, it is even more important to batch!)

On your original code snippet, the resources are closed in a catch block. Which means they only get closed on exception. They should be in a finally block so they get closed regardless.
Rajesh Veluchamy
Ranch Hand

Joined: Jan 15, 2008
Posts: 47
now i have closed in finally block,
then also same problem

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-01000 maximum open cursors exceeded
 
Similar Threads
JSP Help Regarding the Radio Button
Display results with next and previous buttons
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
error "ORA-01000: maximum open cursors exceeded"