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

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 = "{? =  callmy_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) ASBEGIN     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 = "{ callmy_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

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,) ASBEGIN    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();

For Procedure:

CREATE OR REPLACE PROCEDURE my_procedure(p_val IN VARCHAR2,   my_cursor OUT SYS_REFCURSOR) ASBEGIN  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();