Oracle Stored Procedure With Multiple OUT Ref_Cursors
Description
Attachments
1
Activity
Show:
Details
Details
Assignee
Unassigned
UnassignedReporter

Components
Affects versions
Priority
Created May 17, 2018 at 7:39 PM
Updated May 17, 2018 at 7:39 PM
I’m having issues using the StoredProcedureQuery class to return multiple Oracle REF_CURSORs. I’m using Oracle DB 12c, and Hibernate 5.3.0.Final. Using org.hibernate.dialect.Oracle12cDialect as the dialect. The stored procedure query only returns the first result set - it returns an empty list for the second cursor.
StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("SCOTT.HR_DATA.GETCURSORS")
.registerStoredProcedureParameter("EMP_ID", Integer.class, ParameterMode.IN).setParameter("EMP_ID", 4)
.registerStoredProcedureParameter("EMP_C", Class.class, ParameterMode.REF_CURSOR)
.registerStoredProcedureParameter("DEPT_C", Class.class, ParameterMode.REF_CURSOR);
The StoredProcedureQuery spec says:
/**
Retrieve the list of results from the next result set.
The provider will call <code>execute</code> on the query
if needed.
A <code>REF_CURSOR</code> result set, if any, will be retrieved
in the order the <code>REF_CURSOR</code> parameter was
registered with the query.
@return a list of the results or null is the next item is not
a result set
@throws QueryTimeoutException if the query execution exceeds
the query timeout value set and only the statement is
rolled back
@throws PersistenceException if the query execution exceeds
the query timeout value set and the transaction
is rolled back
*/
List getResultList();
I’ve also tried calling storedProcedureQuery.hasMoreResults() between the two getResultList calls, which returns true, but then the next getResultList calls an invalid column name exception.