title: How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA
body: <p>I have an Oracle function which return sys-refcursor and when I call this function using Hibernate, I am getting the following exception.</p>
  
  <pre><code>Hibernate: { ? = call my_function(?) }
   org.hibernate.exception.GenericJDBCException: could not execute query
  javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1360)
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1288)
      at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:313)
  </code></pre>
  
  <p>How can I resolve this?</p>
  
  <p><strong>Oracle function</strong></p>
  
  <pre><code>create or replace 
  FUNCTION my_function(p_val IN varchar2)
      RETURN SYS_REFCURSOR
    AS
      my_cursor SYS_REFCURSOR;
    BEGIN
      OPEN my_cursor FOR SELECT emp_name FROM employees
      WHERE lower(emp_name) like lower(p_val||'%');
      RETURN my_cursor;    
    END;
  </code></pre>
  
  <p><strong>My Entity class</strong></p>
  
  <pre><code>@Entity
  @javax.persistence.NamedNativeQuery(name = "getFunc", query = 
  "{ ? = call my_function(:empName) }", 
   resultClass = Employee.class, hints = 
   { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })
   @Table(name = "EMPLOYEES")
  </code></pre>
  
  <p><strong>and in DAO</strong> </p>
  
  <pre><code>    @Override
          public void findEmployees(QueryData data,
                  String empName) {
  
          List query = (List) entityManager.createNamedQuery("getFunc")
                           .setParameter("empName", empName)
                           .getSingleResult();
                  data.setResult(query);
  }
  </code></pre>