This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "error "ORA-01000: maximum open cursors exceeded"" Watch "error "ORA-01000: maximum open cursors exceeded"" New topic
Author

error "ORA-01000: maximum open cursors exceeded"

Rahul Shivsharan
Ranch Hand

Joined: Jun 17, 2009
Posts: 83
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

Joined: Feb 27, 2009
Posts: 12
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

Joined: May 14, 2004
Posts: 515
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

Joined: Feb 26, 2008
Posts: 166
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


If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
Arnav Velimala
Ranch Hand

Joined: Jun 04, 2007
Posts: 37
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: error "ORA-01000: maximum open cursors exceeded"