JPA and SYS_REFCURSOR like OUT parameter

Question

I want to call a procedure using JPA with SYS_REFCURSOR like OUT parameter. This is very easy using plain JDBC but I'm not sure that is possible in JPA.

My procedure is like following:

CREATE OR REPLACE FUNCTION FN_GET_COINS
  RETURN SYS_REFCURSOR
IS vCursor SYS_REFCURSOR;

BEGIN
  OPEN vCursor FOR
    SELECT
      ...
  RETURN vCursor;
  CLOSE vCursor;

EXCEPTION
  ...
END FN_GET_COINS;

Answer 1

JPA 2.0 has no support for stored procedures, but support has been added in JPA 2.1, part of Java EE 7. Examples of standard JPA 2.1 code using Oracle SYS_REF_CURSOR:

http://wiki.eclipse.org/EclipseLink/Release/2.5/JPA21#Ref_cursor_Example
http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#JPA_2.1_StoredProcedureQuery

---

• EclipseLink 2.5 supports JPA 2.1 (it's the reference implementation):

  http://www.eclipse.org/eclipselink/releases/2.5.php
  https://glassfish.java.net/

• Hibernate 4.3.11 supports JPA 2.1:

  http://www.hibernate.org/downloads

• Hibernate up to 4.2 supports stored procedures via native API & config (non-JPA) :

  http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#sp_query

  (Here the CURSOR must be either returned via a 'stored function', or must be the first parameter (out) of a stored procedure).

  Additionally, Polpan's answer here show how this can be done with a JPA 2.0 native query with a QueryHint, setting Hibernate proprietary hint property org.hibernate.callable to true.