File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Maximum open cursors exceeded" Watch "Maximum open cursors exceeded" New topic
Author

Maximum open cursors exceeded

Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
I am getting this error after executing my java application for some 10-15 minutes. I execute some sql queries in that time.

Message is : ORA-01000: maximum open cursors exceeded

How do i correct it?
Thanks in advance,
Sankar
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Faithfully call close() on your ResultSets once you're done with them. Also take care to close your (Prepared)Statements and your database connections. Ensure that this happens by using try .. catch .. finally - the close() statements belong in the finally clause.
It may be useful to know that when you close a Statement, all its result sets are automatically closed. So as long as the statements are closed in a finally block you can get away with closing the ResultSets outside one.
Do not think that closing the Connection will release all resources like open statements and the like. Not if you are using a connection pool
- Peter
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
I will give u more info on that problem. I am calling oracle-functions thru jdbc in java application. I will give you one sample function

"return types.cursortype
as l_cursor types.cursorType;
begin
open l_cursor for select * from DTMaster;
return l_cursor;
end; "

I am opening the cursor and i am not closing them, simply bcos i cant return the cursor, if i close. I am sure that the cursors opened inside various function calls are giving the problem.
Hope this gives relevant info.
Can u tell me, how to return a set of rows trhough a function other than cursor? It will be very helpful to me.
Thanks,
Sankar
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

that is a typical stored procedure, but it's the java portion that is leaving the cursors open. Maybe if you posted the java portion of the statement/resultset lifespan of code, we may be able to help you better.
jamie
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
I am pasting java code which is used to get data from database.
public ResultSet getData(Connection con, ArrayList proc)
{
ResultSet rs;
CallableStatement callStmt;
String procName = (String)proc.get(0);
proc.remove(0);
int noOfParams = proc.size();
StringBuffer temp = new StringBuffer("begin :1 := " + procName);
if ( noOfParams != 0 ) temp.append("(");
for(int i = 0; i< noOfParams;i++)
{
if ( proc.get(i) instanceof Integer )
{
temp.append(proc.get(i) + " ,");
}
else
{
temp.append("'" + proc.get(i) + "' ,");
}
}
if ( noOfParams != 0 )
{
temp.setLength(temp.length() - 1);
}
if ( noOfParams != 0 ) temp.append(")");
temp.append("; end;");
String sql = new String(temp);
try
{
callStmt = con.prepareCall(sql);
//System.out.println
callStmt.registerOutParameter(1,OracleTypes.CURSOR);
callStmt.execute();
rs = (ResultSet)callStmt.getObject(1);
return rs;
}
catch(SQLException e)
{
return null;
}
}
Looking forward to ur reply guys, peter and jamie,
Sankar
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

The problem looks simple.
you never close your callable statement. You can't. You may be able to close the resultset, but never the statement. It is out of scope by the time you want to close it. Therefore, it never releases the database resources and after some processing, the maximum resources for that connection are exceeded.
You can correct this in 2 ways:
1. close the statement within the scope of the method. In order to do this you have to close the resultset and statement in scope. This means that you can not return the resultset itself. You would have to copy the results to an ArrayList or something, then return the ArrayList instead of the ResultSet.
2. You can make the Statement a class variable. Create the resultset from this class level statement. This way you could return the resultset to the calling method, and once you are finished with the resultset you could have a method like closeStatement() or something like that to close the statement variable. This would free up the DB resources as if you closed the method in scope.
I hope I explained option 2 well enough,
let me know what you decide
Jamie
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi Jamie,
Thanks a lot for ur reply. I have SerializableResultSet class defined by me. So i will pass the data from ResultSet to SerializableResultSet and then return.
Thanks once again,
Sankar
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

It does not really matter what you do with the resultset, the fact remains that "CallableStatement callStmt;" will consume resources and will not be released until you close the connection. I'm positive that this is the reason you are exceeding the maximum allowable cursors(or at least contributing to the problem). So unless you do something like I mentioned before, you will continue to have this problem here.
What method would work best for you? or do you have a different idea?
Jamie

[This message has been edited by Jamie Robertson (edited November 20, 2001).]
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Dear Jamie,
I got ur point and i have closed the callable statement after passing data from ResultSet to SerializableResultSet. So, that will solve the problem, as u said. I implicitly meant that closing of callable statement in my prev. reply. Sorry for not making my point explicitly.
Thanks once again,
Sankar
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

as long as you solved the problem that's all that matters!
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Maximum open cursors exceeded