I’m having issues using the StoredProcedureQuery class to return multiple Oracle REF_CURSORs. I’m using Weblogic 12.2.1.2.0, 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 = getEntityManager().createStoredProcedureQuery(SCHEMA + "." + PACKAGE_NAME + ".transfer_report")
.registerStoredProcedureParameter("p_start_date", Date.class, ParameterMode.IN).setParameter("p_start_date", startDate)
.registerStoredProcedureParameter("p_end_date", Date.class, ParameterMode.IN).setParameter("p_end_date", endDate)
.registerStoredProcedureParameter("p_program", String.class, ParameterMode.IN).setParameter("p_program", program)
.registerStoredProcedureParameter("p_charge_codes", Class.class, ParameterMode.REF_CURSOR)
.registerStoredProcedureParameter("p_transfers", Class.class, ParameterMode.REF_CURSOR)
.registerStoredProcedureParameter("p_final", String.class, ParameterMode.IN).setParameter("p_final", convertBoolToIndicator(finalIndicator));
storedProcedureQuery.execute();
chargeCodes = storedProcedureQuery.getResultList();
for (Object[] row : chargeCodes) {
for (Object obj : row)
System.out.println("charge codesrow value: " + obj);
}
transfers = storedProcedureQuery.getResultList();
for (Object[] row : transfers) {
for (Object obj : row)
System.out.println("transfer row value: " + obj);
}
System.out.println("end");
} catch (Exception e) {
logger.error("Package call failed due to ", e);
throw e;
}
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 call throws and exception.
StoredProcedureQuery.getOutputParameterValue returns a Weblogic specific Cursor class on both resultsets.
Is there any way to return two(or more) REF_CURSORS via Oracle and Hibernate?
