Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

error "ORA-01000: maximum open cursors exceeded"

 
Rahul Shivsharan
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
THE STACKTRACE SAYS THAT THE ERROR IS ON LINE 189, BUT ALL THE PREPAREDSTATEMENTS ARE CLOSED , SO WHATS THE ERROR I DON'T UNDERSTAND

 
Benjamin Hiner
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The result set on line 148 is never closed (not related to your problem though).

I think the issue is that you aren't closing the statements in the while(rs.next()) loop as they are used. On line 199 you close the ResultSet but not the PreparedStatement. In each loop you are getting another prepared statement on line 186 but you only close it outside of the loop.

I haven't ever used jdbc quite like that though. I always have the db calls wrapped up by a DAO / template that does the following steps:

1. Generate sql / read information from the criteria object.
2. Prepare statement.
3. Bind variables.
4. Execute query.
5. Build object(s). -- this may involve other calls that follow these steps
6. Finally clause that closes the one ResultSet and PreparedStatement.

These steps never cross loop boundaries, ever.
 
amit punekar
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Also please put the ResultSet and Statement closing API calls in "finally" block.
Sometimes if you get any exception while processing the resultset , in your code they will never get closed.

Regards,
Amit
 
Jhakda Velu
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I think you should use the same PreparedStatments by clearing the paramaters. Right now you are creating statements in a loop eg


Ditto for other statement in loop.

Though you close the statements by the code below,


you are actually closing the last obtained statement,so if you have looped 100 times, you have closed only 1.

Try and let us know.

Jhakda
 
Arnav Velimala
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ResultSet, Connection, PresparedStatment are all considered to be curosrs here.

1. Yes. As Velu pointed out, the code creates n(n is the size of the 'rs') PreparedStatement objects, but closes only one.
2. Also at line no 162, you are reusing the ResultSet object before closing its previously assigned ResultSet object.

Best Rgds,
Arnav
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic