Hibernate Fails to Handle Multiple REF_CURSOR Outputs with getResultList()

General
1

When calling a stored procedure with 1+ out REF_CURSOR’s, it fails to retrieve the rows when using getResultList().

Per the documentation, it mentions:

“When using REF_CURSOR parameters for result sets, the update counts should be exhausted before calling getResultList to retrieve the result set. Alternatively, the REF_CURSOR result set can be retrieved through getOutputParameterValue. Result set mappings will be applied to results corresponding to REF_CURSOR parameters in the order the REF_CURSOR parameters were registered with the query.”

The last sentence makes it seem as if repeat calls to .getResultList() will move the cursor to the next REF_CURSOR. However, that does not appear to be the cause.

The title mentions how Hibernate “fails” to, however - this might be totally intended.

I have:

StoredProcedureQuery storedProcedureQuery = entityManager
    .createStoredProcedureQuery("SOME_STORED_PROC")
    .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)               
    .registerStoredProcedureParameter(2, Object.class, ParameterMode.REF_CURSOR)
    .registerStoredProcedureParameter(3, Object.class, ParameterMode.REF_CURSOR)  
    .setParameter(1, 123);
    
storedProcedureQuery.execute();

List<Object> firstCursorResultList = storedProcedureQuery.getResultList(); // expected index 2 REF_CURSOR
List<Object> secondCursorResultList = storedProcedureQuery.getResultList(); // expected index 3 REF_CURSOR

This code would result in an error saying the resultList is already closed.

Note: An easy way to fix this is to just use getOutputParameterValue(2) and getOutputParameterValue(3).

2

You’re correct — and you’re not misunderstanding the Hibernate behavior.

Straight Answer:

Calling .getResultList() does not advance between multiple REF_CURSORs. It only retrieves the first cursor result, and trying to call it again will throw an error (like “ResultSet is closed”).

This is expected behavior in Hibernate, and not a bug. The documentation is subtle and easy to misread, but Hibernate requires that you manually retrieve each REF_CURSOR result using getOutputParameterValue(index).

Why this happens:

  • When you use .getResultList(), Hibernate internally assumes a single result set (the first REF_CURSOR).
  • Hibernate does not manage multiple cursors via multiple .getResultList() calls.
  • Attempting to call .getResultList() again after the first closes the cursor and results in the error you’re seeing.

Correct Way (what you already found):

Use:

ResultSet rs1 = (ResultSet) storedProcedureQuery.getOutputParameterValue(2);
ResultSet rs2 = (ResultSet) storedProcedureQuery.getOutputParameterValue(3);

Then manually iterate through each ResultSet and map the rows as needed.

If you’re using JPA with custom result mappings:

You can also use:

List<Object[]> result1 = storedProcedureQuery.getResultList(); // Only works for the first REF_CURSOR

But for multiple cursors:

List<Object[]> result1 = ((ResultSet) storedProcedureQuery.getOutputParameterValue(2)).stream().collect(...);
List<Object[]> result2 = ((ResultSet) storedProcedureQuery.getOutputParameterValue(3)).stream().collect(...);

Or manually build objects from the raw ResultSet.

Summary:

  • .getResultList() only works for the first REF_CURSOR
  • You must use .getOutputParameterValue(index) for multiple cursors
  • This is intended Hibernate behavior, not a bug
    Let me know if you want help mapping the ResultSet to entities or DTOs.