title: Passing a list of values to a stored procedure using JPA 2.1 / Hibernate 4
body: <p>I have a stored procedure that takes in a <em>list of strings</em> (and follows limitations on stored procedures, <a href="https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#sp_query" rel="noreferrer">according to Hibernate docs</a>):  </p>
  
  <pre><code>PROCEDURE count_active_esc(p_count OUT NUMBER, p_codes IN string_list);
  </code></pre>
  
  <p>where <code>string_list</code> is a custom type:</p>
  
  <pre><code>CREATE OR REPLACE TYPE string_list IS TABLE OF VARCHAR(100)
  </code></pre>
  
  <p>and want to to call it from JPA entity manager (<a href="http://www.oracle.com/webfolder/technetwork/tutorials/obe/java/Stored_Proc/StoredProcedures.html#" rel="noreferrer">a new feature in JPA 2.1</a>).</p>
  
  <p>I tried to use an array:</p>
  
  <pre><code>StoredProcedreQuery query = entityManager.createNamedStoredProcedureQuery("count_active_esc");
  query.registerStoredProcedureParameter("p_count", Integer.class, ParameterMode.OUT);
  query.registerStoredProcedureParameter("p_codes", String[].class, ParameterMode.IN);
  query.setParameter("p_codes", new String[] { "AEST" });
  
  query.getOutputParameterValue("p_count"); // &lt;&lt;&lt;&lt;&lt; throws an exception
  </code></pre>
  
  <p>and got an exception:</p>
  
  <pre><code>    PLS-00306: wrong number or types of arguments in call to 'COUNT_ACTIVE_ESC'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
  </code></pre>
  
  <p>And if I pass in a list:</p>
  
  <pre><code>StoredProcedreQuery query = entityManager.createNamedStoredProcedureQuery("count_active_esc");
  query.registerStoredProcedureParameter("p_count", Integer.class, ParameterMode.OUT);
  query.registerStoredProcedureParameter("p_codes", List.class, ParameterMode.IN); // &lt;&lt;&lt;&lt;&lt; throws an exception
  query.setParameter("p_codes", new ArrayList&lt;String&gt; {{ "AEST" }});
  
  query.getOutputParameterValue("p_count");
  </code></pre>
  
  <p>it throws <code>java.lang.IllegalArgumentException: Type cannot be null</code></p>
  
  <p>Also adding <code>query.setHint("org.hibernate.callable", "true")</code> didn't help.</p>
  
  <p>What is the correct way of doing this in JPA or Hibernate? Is it at all possible without resorting to JDBC?</p>
  
  <p>Environment: JPA 2.1, Hibernate 4.3.5, Oracle</p>