• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle CallableStatement

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic