Oracle CallableStatement

[Ernie Stephenson]

Hello,

I've been having some trouble with a CallableStatement against an oracle database. The statements have not been closed properly and leave cursors open on the connection pool. Now I have looked at the doc's and found that you have to use the OracleCallableStatement resultset etc. But this does not seem to be possible from a tomcat datasource since I get a ClassCastException when I try to cast into either of these... I think the root of the problem is Oracle, but I would like to be able to get hold of an OracleConnection from Tomcat and I can't get it? Can any on help out there?

Cheers,

Ernie

[Paul Sturrock]

statements have not been closed properly and leave cursors open on the connection pool

Why are you not closing the connections? Don't you have access to the JDBC code?

OracleConnection? What benefits do you hope to get by explicitly getting a vendor specific connection? I'm not sure I understand what you are trying to do.

[Ernie Stephenson]

I'd rather not use Oracle specific stuff but the code i use calls a stored procedure. When i close the connection, then free to the pool (i'm using a tomcat pool here), the cursors against the connection remain open for the call to the stored procedure... When i try another way and use the Oracle specific stuff this does not happen... Typical Oracle..

Ernie

[Paul Sturrock]

Right I still don't follow what you are doing. If I understand you right - you are obtaining a Connection from a connection pool and using it to call a stored procedure then closing the connection yes? And when you do this cursors remain open. But when you do it specifically calling an OracleConnection (?) this doesn't happen. And this is what I don't understand - if you are getting an implementation of the java.sql.Connection interface and you are using one of the Oracle JDBC drivers then you are getting a an instance of oracle.jdbc.driver.OracleConnection. And I presume when you are using "Oracle specific stuff " you are not also using a connection pool?

Perhaps you can post your code and this might help me understand what it is you are trying to do?

[Ernie Stephenson]

Hello,

The code below is abbreviated. If you try this against an Oracle database you will (from a Tomcat pool) have a connection, with cursors left open. If you could cast the ResultSet/CallableStatement to the Oracle versions then you will not get the cursors left open...

...
CallableStatement stmt = null;
ResultSet rs = null;
List hotels = new ArrayList();
try
{
// prepare the call
stmt =
getConnection().prepareCall(
"{? = call PACKAGE.search(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

// bind the parameters
int count = 1;
stmt.registerOutParameter(count++, OracleTypes.CURSOR);

// ... bind all the stuff

// grab the results
stmt.execute();
rs = (ResultSet)stmt.getObject(1);
while (rs.next())
{
Thing thing= new Thing();

// grab the results
count = 1;
thing= .setX(rs.getString(count++));
thing= .setY(rs.getString(count++));
thing= .setZ(rs.getString(count++));

// add to list
things.add(avail);
}
}
catch (IOException e)
{
// Log this error
LoggerHelper.log(logger, Priority.ERROR, e);

// throw an exception
throw new SQLException("");
}
catch (SQLException e)
{
// Log this error
LoggerHelper.log(logger, Priority.ERROR, e);

// throw an exception
throw new SQLException("");
}
finally
{
// clean up
close(rs);
close(stmt);
closeConnection();
}

// return the things
return things;
...

Cheers,

Ernie