• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Number of Cursors exceeded exception due to another connection before closing the original one.

 
es kranthi
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are no open Connections ,resultSet or PreparedStatements in my code.
However we are running queries simultaneously in a for loop.

There is a function that is called inside the same loop which is used to get sequences from DB inside the same for loop.
This called function has another connection made and closed inside it.
Is the cursor exceeded exception occurring due to this.There is no problem when i execute to obtain 10 records however if i want more than 10 records i get the Number of cursors exceeded exception.


Ex :


}


One of my friends said that i need to close the original connection before opening the connection in another function as this might lead to Cursors exceeded exception if there are more records.
Any help in this regard.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database are you using?

And are those catch blocks really empty?
If so I would suggest logging the exceptions you are getting (if any). Failing silently is not a good idea.

Where do you get your connections from? Is it a connection pool or are they sharing a connection?
That code does not show the opening of connections or, in the findSequences part, the opening or closing of them.
 
es kranthi
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dave for answering.

What database are you using?


Oracle 11g

And are those catch blocks really empty?

Nope. We are fetching some data say 500+ records from the DB each record holding more than 50 columns. Catch block is where we are geting the exception .Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

We are getting connection from the pool.
FetchSequences is just used to get the sequences and its shown just for example.
My emphasis here is to point out that we are opening another connection inside a loop which has an old connection object. So i left that blank.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something is definitely not being closed, unless someone else is using that database.
Doesn't matter how much you are certain everything is closed, a "max cursors" error means result sets are being left open.

Are there any other exceptions occurring?
 
es kranthi
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No only that exception.
Is there any tool or way to identify an unclosed cursors ?

Somehow i found the solution myself.
If there is another connection being opened on top of the Original connection inside a loop we are facing this issue.
Now we removed the extra connection and tried and it worked .
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic