How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA

Question

I have an Oracle function which return sys-refcursor and when I call this function using Hibernate, I am getting the following exception.

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)

How can I resolve this?

Oracle function

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;

My Entity class

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

and in DAO

    @Override
        public void findEmployees(QueryData data,
                String empName) {

        List query = (List) entityManager.createNamedQuery("getFunc")
                         .setParameter("empName", empName)
                         .getSingleResult();
                data.setResult(query);
}

Answer 1

Oracle function or a stored procedure can be called using EntityManager in the following manner.

For Oracle Function

Create a function with sys_refcursor as return type

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;

In Entity class, define function as

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

For Oracle Stored Procedure

Create procedure with sys_refcursor as first OUT parameter

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||'%');

In Entity class define procedure as

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

and finally in DAO class, call function or procedure as

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

Thanks

Answer 2

For your function,

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;

You can define the following NamedNativeQuery:

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

And, you can call the query like this:

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

For a stored procedure:

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;

, you can use the following JPA 2.1 query:

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<Object[]> result = query.getResultList();

Answer 3

For Procedure:

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;

Alternative Solution: Call procedure with sys_refcursor as OUT parameter without defining @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();

Answer 4

You seem to be confusing Oracle functions with Oracle stored procedures.

Functions can be invoked from a select statement - user defined functions like yours act the same way as the built-in functions, like min() and max(). They cannot be invoked by an external "call" like stored procedures can.

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions231.htm#i1012049 for the definition of a function.

You probably will need to re-write your function as a stored procedure.

Answer 5

JPA 2.1 early draft states that there will be support for stored procedures, according to Arun Gupta from Oracle.

Support for Stored Procedures: Added support for the invocation of predefined database functions and user-defined database functions to the Java Persistence query language.

There are different variants of EntityManager.createXXXStoredProcedureQuery methods that return a StoredProcedureQuery for executing a stored procedure. Just liked @NamedQuery, there is @NamedStoredProcedureQuery that specifies and names a stored procedure, its parameters, and its result type. This annotation can be specified on an entity or mapped superclass. The name specified in the annotation is then used in EntityManager.createNamedStoredProcedureQuery. The IN, OUT, and INOUT parameters can be set and used to retrieve values passed back from the procedure. For example:

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

As for when the spec is going to be finalized, or when Hibernate will support JPA 2.1, I can't say. But it might be worth keeping an eye out.

Answer 6

The previous solution doesn´t seem to work with eclipselink. I got this running with native queries under JPA with

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