This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Maximum open cursors exceeded

 
Shiv Sidhaarth
Ranch Hand
Posts: 119
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3252
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
as long as you solved the problem that's all that matters!
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic