JPA and SYS_REFCURSOR, such as the OUT parameter

Question

I want to call a procedure using JPA with parameter SYS_REFCURSOR as OUT . It is very simple using simple JDBC, but I'm not sure if this is possible in JPA.

My procedure is similar to the 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 does not support stored procedures, but support was 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 (this is a 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

• Hibernation up to 4.2 supports stored procedures through its own API and config (not JPA):

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

  (Here CURSOR should either be returned via the "stored function", or it should be the first parameter (from) of the stored procedure).

  In addition, Polpan's answer here shows how this can be done using a regular JPA 2.0 query with QueryHint by setting the hinternate property for hibernate org.hibernate.callable to true.

Answer 2

If you want to do this with standardized JPA 2.0, you're out of luck (besides hacking it through the "native queries" API ... it is standardized in JPA2.1.

DataNucleus JPA has supported JPA 2.1 syntax since the beginning of 2012 (as stated in the original answer, so you should not understand what the "current answer is outdated"), shown in these documents http://www.datanucleus.org/products/accessplatform_3_3/jpa /stored_procedures.html