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

1 Like
#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.

#5

They are bugs in OJDBC driver forcing you to ignore some errors to get the result, you can do it “easily” when using direct JDBC but using StoredProcedure probably requires modification of the Oracle12cDialect.

You have first to ignore “ORA-17283: No resultset available” and if the execute() of the statement returns false you have still to test if “getUpdateCount()” returns -1 in which case you have to call “getMoreResults()” one more time.

#6

Hi there. Thanks for the information :slightly_smiling_face: