• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql statement, implicitly open cursor and oracle session...

 
Vin Man
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
In my application, I have a JDBC connection which I create and re-use over and over. The connection is used to execute sql statement, whose resultset is stored in bean object. So the sequence of executing an SQL is as outlined below:
.
.
.
String sql = "select customerName, address from customer";
Connection connection = getPreviouslyCreatedconnection(); // returns valid connection
Statement statement = connection.createStatement();
storeResultSetInBean(statement.executeQuery(sql));
.
.
.
everything is working as expected - for a while any way.
The problem is each time a sql is executed, an oracle session is created to hold the implicitly opened oracle cursor, and it remains open as long as the connection is opened!!! So my application can only operate until the maximum number of cursors defined by Oracle is reached, at which point I will get ora###-Maximum cursors reached when I try to execute an sql. I don't really want to close my connection each time I need access to the db, as it can slow down my app.
I have tried the statement.close() at the end of each sql operation, but that seems to close the connection as well.
Thanks in advacne for any idea how to get around this problem...
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Vin Man:
Hi,
In my application, I have a JDBC connection which I create and re-use over and over. The connection is used to execute sql statement, whose resultset is stored in bean object. So the sequence of executing an SQL is as outlined below:
.
.
.
String sql = "select customerName, address from customer";
Connection connection = getPreviouslyCreatedconnection(); // returns valid connection
Statement statement = connection.createStatement();
storeResultSetInBean(statement.executeQuery(sql));
.
.
.
everything is working as expected - for a while any way.
The problem is each time a sql is executed, an oracle session is created to hold the implicitly opened oracle cursor, and it remains open as long as the connection is opened!!! So my application can only operate until the maximum number of cursors defined by Oracle is reached, at which point I will get ora###-Maximum cursors reached when I try to execute an sql. I don't really want to close my connection each time I need access to the db, as it can slow down my app.
I have tried the statement.close() at the end of each sql operation, but that seems to close the connection as well.
Thanks in advacne for any idea how to get around this problem...

seems valid as it looks like you never call statement.close() leaving the resource on the oracle side waiting for you to do something. A way to get around this is to
1. retrieve the resultset
2. create a collection to hold the resultset info
3. close your db resources
4. pass the collection through the method instead of your resultset.
OR if possible ( not likely ):
Jamie
 
Vin Man
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jamie,
Thanks for your response.
I did find that the statement.close() method does release any Oracle and JDBC resource involved. Of course, as you have stated, one would need to store the resultset into some collection object prior to doing so.
I also found another bazzard thing - if you access your database using the connection.callableStatement, you would leave some dangling opened cursor in your Oracle server even if you close your callableStatement!!! I have included my code for illustration:
...
...
Connection con;
CallableStatement cs;
try{
con = getDBConnection();
cs = con.prepareCall(
"{? = call checkPriv(" +
userID + ")}");
try{
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.execute();
canUpdate = (cs.getInt(1) >= 0);
}
finally{
cs.close();
if (con != null)
returnConnectionToPool(con);
}
As you see cs.close() is guaranteed to run, but the implicitly opened cursor by the sql inside checkPriv() are still left dangling in Oracle v$open_cursor!!!
Let me know if you experience differently.
thanks.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
interesting...but I think I've heard of this problem before. I think the solution was to do a con.rollback() before or in your returnConnectionToPool(con); method. If that doesn't work, just for curiosity's sake, if you do a con.close() does that clear the cursors?
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic