Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:
3.0
Deal All,
We are facing this issue where RefCursors in Oracle 10g are not closing. The code used for invoking the stored procedures is very simple....
Code:
Session session = getSession();
Connection connection = session.connection();
CallableStatement callableStatement = null;
ResultSet resultSet = null;
List clientIdList = new ArrayList();
try{
callableStatement = connection.prepareCall("{ ? = call DATA_OWNER.PT_GETDATA_VALUES.USP_GET_CLIENT() }");
callableStatement.registerOutParameter(SystemConstants.P_OUT, oracle.jdbc.driver.OracleTypes.CURSOR);
callableStatement.executeQuery();
resultSet = (ResultSet)callableStatement.getObject(SystemConstants.P_OUT);
//Iterate over resultSet and fetch all the clientIds
while(resultSet.next()){
Client client = new Client();
client.setClientId(new Integer(resultSet.getString(SystemConstants.CLL_ID)));
client.setClientName(resultSet.getString(SystemConstants.CLL_CLIENT_NAME));
clientIdList.add(client);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
callableStatement.close();
connection.close();
session.close();
} catch(Exception e) {}
}
return clientIdList;
I believe, as we are closing the resultSet, callableStatement, connection and to session as well, the refcursor should be closed.
The problem I am facing now is that the PLSQL Developers and DBAs believe, it is because of the way Hibernate obtains the session object that the cursors remain open.
I am sure this is not the case, but it is difficult to convince my peers.
I would also like to mention that the Database is layered...i.e. the Java app talks to one schema(say parent) and this schema then invokes stored procedures in the child schema. This means that the Java App never invokes Stored Procedure in the child schema.
Can anybody please give me pointers as to where could the problem be? Its pushing to an extent now that we are being asked to stop using Hibernate.
Shardul.
Login
FAQ
Search
