We are facing an issue while calling the stored procedure from the application.
The database is oracle 10g. This proc has 2 input parameters and 2 output parameters.

This worked with Hibernate 3.2 and fails with Hibernate 4.0 since we cannot get a handle to session.getConnection.

Input 1:- DB-List (Array)
Input 2:- String

Output 1:-Again a DB-List (Array)
Output 2:- Number


When we are trying to use
Query q = session.createSQLQuery("{call proc_name(?,?,?,?)}");
We cannot distinguish between in parameters and out parameters.
So how should we handle it by using this.

Also,
We tried to use callable statement as follows:-
Session session = (Session) getEntityManager().getDelegate();
SessionImpl sessionImpl = ((SessionImpl) getEntityManager().getDelegate());
Connection cc = sessionImpl.connection();
CallableStatement callableStatement = null;

callableStatement = cc.prepareCall("{call proc_name(?,?,?,?)}");
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(DB_LIST",callableStatement.getConnection());
ARRAY paramArray = new ARRAY(descriptor, callableStatement.getConnection(), array);
callableStatement.setArray(1, paramArray);
callableStatement.setString(2, "N");
callableStatement.registerOutParameter(3, OracleTypes.ARRAY, "DB_RETURN_LIST");
callableStatement.registerOutParameter(4, Types.INTEGER);
// executing the query
callableStatement.execute();

We get the following error:-
javax.ejb.EJBException: java.lang.ClassCastException: $Proxy50 cannot be cast to oracle.jdbc.OracleConnection

Can you please provide some suggestions.


We have also tried doWork()
we got the handle to the connection but still the ClassCastException

JBAS014134: EJB Invocation failed on component HoldoversServiceBean for method public void

java.lang.Exception: javax.ejb.EJBException: java.lang.ClassCastException: $Proxy66 cannot be cast to oracle.jdbc.OracleConnection

The connection instance is {}org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler@1a0d62a[valid=true]

1. Hibernate Entity Manager works if we do not use ARRAYS. So the problem is within the Oracle ArrayDescriptor.

2. Trying documented workarounds to fetch an Oracle connection instead of the Hibernate Proxy connection. Apparently fetching the oracle connection should allow running ArrayDescriptor.

a. 2 methods of fetching the Oracle connection so far have failed.

We have tried suggestions given at http://stackoverflow.com/questions/11524984/executing-native-query-with-hibernate-4-1 and http://stackoverflow.com/questions/10731266/sessionfactory-opensessionconnection-in-hibernate-4

This is fixed.

We can get the underlying connection using this code


session.doWork(
new Work() {
public void execute(Connection oc)
throws SQLException {
//Solution 1
oracle.jdbc.driver.OracleConnection oc1 = (oracle.jdbc.driver.OracleConnection) oc.getMetaData().getConnection();
oc1.setClientIdentifier("ta");


}
}
);