0 Replies Latest reply on Nov 6, 2012 3:18 AM by hardik dhanuka

    how to call stored procedure with multiple in and out parameters using entity manager and jboss

    hardik dhanuka Newbie

      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.
      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
      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.