Oracle Stored Procedure With Multiple OUT Ref_Cursors

Description

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.

Attachments

1

Activity

Show:

Details

Assignee

Reporter

Components

Affects versions

Priority

Created May 17, 2018 at 7:39 PM
Updated May 17, 2018 at 7:39 PM