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.
Input 1:- DB-List
Input 2:- String
Output 1:-Again a DB-List
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.
