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.

Answer 2

If you want to do it with standardised JPA 2.0 then you're out of luck (apart from hacking it through the "native query" API ... it is standardised in JPA2.1.

DataNucleus JPA has supported the JPA 2.1 syntax since early 2012 (as said in the original answer, so no idea what the "current answer is out of date" is supposed to mean), shown in these docs http://www.datanucleus.org/products/accessplatform_3_3/jpa/stored_procedures.html