Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ORA-01000 maximum open cursors exceeded

 
Rajesh Veluchamy
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



Thanks in advance
 
fred rosenberger
lowercase baba
Bartender
Posts: 12126
30
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rajesh Veluchamy
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rajesh Veluchamy
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
now i have closed in finally block,
then also same problem

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic