Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

maximum open cursor ... please help

 
deekasha gunwant
Ranch Hand
Posts: 396
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I'm facing a strange problem in my java application.
I'm connecting to Oracle database using jdbcodbc driver.
My application consists of swing GUI . It populates a number GUI elements like JList/Jcombo.... in a number of panels using the data retrieved from oracle db.
It again uses the parameters selected by the user to generate various reports/graphs.(this again requires firing no. of executequery statements.)
now the problem is whenever we are using it to generate one report after another then after some time things don't work correctly and in the java console maximum open cursor exception is written.
restarting the application solves the thing but after some time the problem crops up again.
In my knowledge we have closed all statements/resulsets once we are through with them.
solving this problem is very crucial for my project.
looking forward to the replies from all fellow ranchers.
regards
deekasha
 
narayan kulkarni
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Deeksha
There is a limit on number of cursors that can be kept open at a time in oracle
Try to close the connection to database everytime and open a new connection everytime
Its not advisable but check once again on closing of Statements/ResultSet
Try Connection pooling feature of jdbc 2.0
This is what I tied and it worked and it worked for me
I would like to know if there is any other method
Regards
narayan

[This message has been edited by narayan kulkarni (edited February 01, 2001).]
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by narayan kulkarni:
There is a limit on number of cursors that can be kept open at a time in oracle

True. But this is a configurable limit and its default is quite low (50 or so). You can happily up that to 800 without bringing a decent Oracle box to its knees.
Unfortunately I cannot tell you which of the gazillion parameters to tweak, but it's there -- search the docs.
- Peter

 
deekasha gunwant
Ranch Hand
Posts: 396
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much narayan and Peter for your prompt replies.
narayan,in our project we are having a global class that is having one static Connection object and one static Statement object.
These Objects are created when the application starts and throughout the life of the aplication we are using same connection and statement object again and again.It's only the resultset objects that we are creating everytime we need to execute a query and we are closing it as soon as we are done with them.
since there is only one connection object per application session i don't think connection pooling will be of much help.
and as far as configuring the oracle is concerned.yes peter i can configure oracle to 700-800 cursor limit but the problem is that i'm getting this maximum open cursor exception when my application's only one instance is running . but actually this application needs to be run on several machines at our client's place so i don't think even increasing the oracle limit to 700-800 will be a permanent solution to my problem.

by the way ...We are using jdbc-odbc driver can this be a cause of problem
hope to find some more help from narayan,peter and all others
regards
deeksha
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by deekasha gunwant:
i can configure oracle to 700-800 cursor limit but the problem is that i'm getting this maximum open cursor exception when my application's only one instance is running . but actually this application needs to be run on several machines at our client's place so i don't think even increasing the oracle limit to 700-800 will be a permanent solution to my problem.

This is a per-session limit (database session, but it sounds as if this will map 1:1 to your client session). So it may work after all. Of course if there is a less wasteful solution that is preferable.
From your description it sounds as if you do not really need those cursors, but have a "cursor leak" that keeps the cursors allocated even though you close the result sets? Is it feasible to get rid of it by more aggressively cleaning up your statements and results?

by the way ...We are using jdbc-odbc driver can this be a cause of problem

Well it certainly won't help performance, and who knows how it behaves differently... Oracle have native JDBC drivers. Try those.
- Peter
 
Matthias Cullmann
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Me too, I had this problem. Mee too I have one global Connection variable and a connect function, returning a Connection to my DB.
For querys I use the following method, which deals with the error:
ResultSet query( String s ) throws Exception{
ResultSet rs = null;
try {
Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
stmt.setMaxFieldSize(0);
stmt.setMaxRows(0);
rs = stmt.executeQuery(s);
}
catch (Exception e) {
conn.close();
conn = connect( driver, driverURL, user, pass );
rs = query( s );
}
return rs;
}
Note:
Reconnect only on Error.
Recursive try - catch. No Error will make his way out of here, harharhar !
[ January 16, 2003: Message edited by: Matthias Cullmann ]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the error occurs because you do not close the statement ( or resultset for that matter ) in your method. You can not guarantee that it will be closed outside the method. A better approach is to save the resultset to a List ( or Collection ), then close all resources ( resultset/statments ) and return the List instead of the resultset.
Jamie
 
Mike Brock
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cursors in Oracle's JDBC driver are bound to Statement. You can close a cursor simply by calling close() on the Statement object when you are done with it.
Mike.
[ January 22, 2003: Message edited by: Mike Brock ]
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic