Cómo llamar a Oracle Function o Procedure utilizando Hibernate (EntityManager) o JPA 2

Tengo una función de Oracle que devuelve sys-refcursor y cuando llamo a esta función usando Hibernate 4, recibo la siguiente excepción.

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) 

¿Cómo puedo resolver esto?

Función de Oracle

 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; 

Mi clase de Entidad

 @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") 

y en DAO

  @Override public void findEmployees(QueryData data, String empName) { List query = (List) entityManager.createNamedQuery("getFunc") .setParameter("empName", empName) .getSingleResult(); data.setResult(query); } 

La función de Oracle o un procedimiento almacenado se puede llamar utilizando EntityManager de la siguiente manera.

Para la función de Oracle

Crea una función con sys_refcursor como tipo de retorno

 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; 

En la clase Entity, define la función como

 @javax.persistence.NamedNativeQuery(name = "getFunc", query = "{? = call my_function(:empName) }", resultClass = Employee.class, hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") }) 

Para el procedimiento almacenado de Oracle

Crear procedimiento con sys_refcursor como primer parámetro OUT

 CREATE OR REPLACE PROCEDURE myProcedure(p_cursor out sys_refcursor, p_val in varchar2 ) AS BEGIN OPEN o_cursor FOR SELECT emp_name FROM employees WHERE LOWER (emp_name) LIKE lower(p_val||'%'); 

En la clase Entity define el procedimiento como

 @javax.persistence.NamedNativeQuery(name = "getProc", query = "{ call my_procedure(?,:empName) }", resultClass = Employee.class, hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") }) 

y finalmente en la función de llamada de clase DAO o procedimiento como

 Query query = entityManager.createNamedQuery("getFunc"); // if procedure then getProc query.setParameter("empName","smith"); query.getResultList(); 

Gracias

Este artículo explica con gran detalle todas las combinaciones para llamar a procedimientos y funciones almacenados de Oracle.

Para su función,

 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; 

Puede definir la siguiente NamedNativeQuery :

 @NamedNativeQuery( name = "my_function", query = "{ ? = call my_function( ? ) }", callable = true, resultClass = String.class ) 

Y puede llamar a la consulta de esta manera:

 List employeeNames = entityManager .createNamedQuery("my_function") .setParameter(1, 1L) .getResultList(); 

Para un procedimiento almacenado:

 CREATE OR REPLACE PROCEDURE my_procedure(p_val IN VARCHAR2, my_cursor OUT SYS_REFCURSOR, ) AS BEGIN OPEN my_cursor FOR SELECT emp_name FROM employees WHERE lower(emp_name) like lower(p_val||'%'); END; 

, puede usar la siguiente consulta JPA 2.1:

 StoredProcedureQuery query = entityManager .createStoredProcedureQuery("my_procedure") .registerStoredProcedureParameter(1, String.class, ParameterMode.IN) .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR) .setParameter(1, 1L); query.execute(); List result = query.getResultList(); 

Para el procedimiento:

 CREATE OR REPLACE PROCEDURE my_procedure(p_val IN VARCHAR2, my_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN my_cursor FOR SELECT emp_name FROM employees WHERE lower(emp_name) like lower(p_val||'%'); END; 

Solución alternativa: procedimiento de llamada con sys_refcursor como parámetro OUT sin definir @NamedNativeQuery

 StoredProcedureQuery query = entityManager.createStoredProcedureQuery("myProcedure"); query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR); query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN); query.setParameter(2, "Umesh"); List result = query.getResultList(); 

El primer borrador de JPA 2.1 establece que habrá soporte para los procedimientos almacenados, según Arun Gupta de Oracle.

Soporte para procedimientos almacenados: Se agregó soporte para la invocación de funciones de base de datos predefinidas y funciones de bases de datos definidas por el usuario al lenguaje de consultas de Java Persistence.

Existen diferentes variantes de métodos EntityManager.createXXXStoredProcedureQuery que devuelven una StoredProcedureQuery para ejecutar un procedimiento almacenado. Simplemente me gustó @NamedQuery , hay @NamedStoredProcedureQuery que especifica y nombra un procedimiento almacenado, sus parámetros y su tipo de resultado. Esta anotación se puede especificar en una entidad o superclase mapeada. El nombre especificado en la anotación se usa luego en EntityManager.createNamedStoredProcedureQuery . Los parámetros IN, OUT e INOUT se pueden configurar y usar para recuperar valores pasados ​​del procedimiento. Por ejemplo:

 @Entity @NamedStoredProcedureQuery(name="topGiftsStoredProcedure", procedureName="Top10Gifts") public class Product { . . . } // In your client StoredProcedreQuery query = EntityManager.createNamedStoredProcedureQuery("topGiftsStoredProcedure"); query.registerStoredProcedureParameter(1, String.class, ParameterMode.INOUT); query.setParameter(1, "top10"); query.registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN); query.setParameter(2, 100); // there are other setParameter methods for defining the temporal type of a parameter . . . query.execute(); String response = query.getOutputParameterValue(1); 

En cuanto a cuándo se va a finalizar la especificación, o cuándo Hibernate admitirá JPA 2.1, no puedo decirlo. Pero podría valer la pena estar atento.

La solución anterior no parece funcionar con eclipselink. Lo hice funcionar con consultas nativas bajo JPA con

  List result = em.createNativeQuery("select YOURFUNCTION(?) from dual ") .setParameter(1, ONEPARAMETER) .getResultList(); 

Parece confundir las funciones de Oracle con los procedimientos almacenados de Oracle.

Las funciones se pueden invocar desde una instrucción select: las funciones definidas por el usuario como la suya actúan de la misma manera que las funciones incorporadas, como min () y max (). No pueden ser invocados por una “llamada” externa como pueden hacerlo los procedimientos almacenados.

Vea http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions231.htm#i1012049 para la definición de una función.

Probablemente necesite volver a escribir su función como un procedimiento almacenado.