We're updating the issue view to help you get more done. 

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.

Environment

Hibernate 5.3.0.FINAL, JDK 1.8, Oracle 12c, and OJDBC8 12.2.0.1

Status

Assignee

Unassigned

Reporter

Jeff Chatham

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.3.0.Final

Priority

Major