Oracle Stored Procedure With Multiple OUT Ref_Cursors


#1

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?


#2

Try to replicate this issue using this test case template:

http://in.relation.to/2016/01/14/hibernate-jpa-test-case-template/

Then we can open a Jira issue.


#3

I have the issue replicated in a test case and have created this Jira ticket: https://hibernate.atlassian.net/browse/HHH-12596


#4

@khaannn, I am also trying to do the same but not able to find a solution. I have looked at the JIRA as well. Want to know if you find any workaround on this. Thanks for your support.