title: how to call stored procedure with multiple in and out parameters using entity manager
body: <p>We are facing an issue while calling the stored procedure from the application.
  The database is <code>oracle 10g</code>
  This proc has 2 input parameters and 2 output parameters.</p>
  
  <p>Input 1:- DB-List
  Input 2:- String</p>
  
  <p>Output 1:-Again a DB-List
  Output 2:- Number</p>
  
  <p>When we are trying to use</p>
  
  <pre><code> Query q = session.createSQLQuery("{call proc_name(?,?,?,?)}");
  </code></pre>
  
  <p>We cannot distinguish between <code>in</code> parameters and <code>out</code> parameters.
  So how should we handle it by using this.</p>
  
  <p>Also,
  We tried to use callable statement as follows:</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>We get the following error:</p>
  
  <pre><code>javax.ejb.EJBException: java.lang.ClassCastException: 
  $Proxy50 cannot be cast to oracle.jdbc.OracleConnection
  </code></pre>
  
  <p>Can you please provide some suggestions.</p>
  
  <p>This is the Entity Manager that we are using</p>
  
  <pre><code>public abstract class GenericDAO&lt;T, ID extends Serializable&gt; implements IGenericDAO&lt;T, ID&gt; {
  private final Class&lt;T&gt; persistentClass;
  @PersistenceContext(unitName = "firstPersistenceUnit")
  @Produces
  private EntityManager entityManager;
  
  public void setEntityManager(final EntityManager entityManager) throws DataAccessException {
  this.entityManager = entityManager;
  }
  
  public EntityManager getEntityManager() throws DataAccessException {
  return entityManager;
  }
  }
  </code></pre>
  
  <p>Here is the entry in the Persistance.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"&gt;
  &lt;persistence-unit name="firstPersistenceUnit"&gt;
  &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
  &lt;jta-data-source&gt;java:jboss/firstDataSource&lt;/jta-data-source&gt;
  &lt;class&gt;com.domain.Branch&lt;/class&gt;
  &lt;exclude-unlisted-classes&gt;true&lt;/exclude-unlisted-classes&gt;
  &lt;shared-cache-mode&gt;ENABLE_SELECTIVE&lt;/shared-cache-mode&gt;
  &lt;validation-mode&gt;AUTO&lt;/validation-mode&gt;
      &lt;properties&gt;
          &lt;property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" /&gt;
          &lt;property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" /&gt;
          &lt;property name="hibernate.show_sql" value="true"/&gt;
          &lt;property name="hibernate.format_sql" value="true"/&gt;
      &lt;/properties&gt;
  &lt;/persistence-unit&gt;
  
  &lt;persistence-unit name="secondPersistenceUnit"&gt;
  &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
  &lt;jta-data-source&gt;java:jboss/secondDataSource&lt;/jta-data-source&gt;
  &lt;class&gt;com.domain.PocJeeCounty&lt;/class&gt;
  &lt;exclude-unlisted-classes&gt;true&lt;/exclude-unlisted-classes&gt;
  &lt;shared-cache-mode&gt;ENABLE_SELECTIVE&lt;/shared-cache-mode&gt;
  &lt;validation-mode&gt;AUTO&lt;/validation-mode&gt;
      &lt;properties&gt;
          &lt;property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" /&gt;
          &lt;property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" /&gt;
          &lt;property name="hibernate.show_sql" value="true"/&gt;
          &lt;property name="hibernate.format_sql" value="true"/&gt;
      &lt;/properties&gt;
  &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>