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?
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..
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?
Joined: Feb 03, 2004
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...