CODEWITHSUNDEEP
oracle-databasespring-data-jparef-cursor
Zamboo
Zamboo

Reputation: 539

Is it possible to assign multiple Oracle ref cursor to one

As said in the title, I would like to know if it's possible to assign multiple ref cursor (in Oracle Stored procedure) to one: Like the following code: ....

    create or replace procedure assign_ref_cursor(result_cur out sys_refcursor)
    as
    cursor1 sys_refcursor;
    cursor2 sys_refcursor;
    cursor3 sys_refcursor;
    
    -- expected result:
    result_cur[0] = cursor1
    result_cur[1] = cursor2
    result_cur[2] = cursor3
...
return  result_cur;

... Well, what it would be nice, it's to have the whole result of each ref_cursor in one.

The reason for that is because I'm retrieving data (multiple ref cursor) from stored procedure using Spring data JPA. But apparently there is no solution to retrieve multiple ref cursor using javax.persistence.storedprocedurequery... despite days of google search...

So I imagined the solution to change the Oracle stored procedure to return only one ref_cursor, that "aggregate" the different ref_cursor.

Following the question of Alex, here is an UPDATE

I tried to call three OUT ref cursor parameters using:

StoredProcedureQuery proc = entityManager.createStoredProcedureQuery("myStoredProc");
proc.registerStoredProcedureParameter("cursor1", void.class, ParameterMode.REF_CURSOR);
proc.registerStoredProcedureParameter("cursor2", void.class, ParameterMode.REF_CURSOR);
...
List<Object[]> listOfObjects =  proc.getResultList();

But listOfObjects contains only the content of cursor1 No way to get the content of cursor2...

Upvotes: 0

Views: 1333

Answers (2)

Shubhankar Sarkar
Shubhankar Sarkar

Reputation: 49

This might be helpful:

Workaround by getting a connection from Hibernate EntityManager and then using CallableStatement.

// Session = org.hibernate.Session
// entityManager = javax.persistence.EntityManager
Session session = entityManager.unwrap(Session.class);
session.doWork(new Work() {
@Override
public void execute(Connection con) throws SQLException {
    // do something useful
    try (CallableStatement cs = con.prepareCall("{CALL TEST_PACKAGE.GETCURSORS(?,?,?)}")) {
        cs.setInt(1, 1);
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);
        cs.execute();
        ResultSet rs = (ResultSet) cs.getObject(2);
        ResultSet rs1 = (ResultSet) cs.getObject(3);
        while (rs.next()) {
            int a = rs.getInt(1);
            System.out.println(a);
        }
        while (rs1.next()) {
            int b = rs1.getInt(1);
            System.out.println(b);
        }
    }
}

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191530

If you currently have something like:

open cursor1 for <query1>;
open cursor2 for <query2>;
open cursor3 for <query3>;

then you could combine them as cursor expressions in one result:

open result_cur for
  select cursor(<query1>) as cursor1,
         cursor(<query2>) as cursor2,
         cursor(<query3>) as cursor3
  from dual;

If you're returning other data you can include more 'normal' columns as well and make that main query against a real table instead of dual, of course.

Upvotes: 1

Related Questions