Unable to map values returned in SYS_REFCURSOR

[Jumna Mohammed]

Hi!

I have recently started to use jooq and am facing an issue when trying to map the output returned from a Proc as a SYS_REFCURSOR.

We are using Oracle database and procedure DDL is as below:

PROCEDURE read_accountHierarchy(

            in_period IN VARCHAR2,

            out_hierarchy OUT sys_refcursor);

And the cursor is defined as below:

PROCEDURE read_accountHierarchy(

            in_period IN VARCHAR2,

            out_hierarchy OUT sys_refcursor )

    AS

    BEGIN

    OPEN out_hierarchy FOR 

       SELECT hierarchy_type ( top_level_bank_id,top_level_account_number, hierarchy_type , 

        CAST ( MULTISET (SELECT hierarchy_account_type( hierarchies.bank_id,hierarchies.account_number,hierarchies.acc_level,

                                                     hierarchies.account_path_ids  ) FROM ...

When I try to put the Result<Record> into List<HierarchyTypeRecord>, it throws an error:

Caused by: org.jooq.exception.DataTypeException: Cannot cast from class java.lang.Object (instance type: class oracle.sql.STRUCT to class java.lang.Object

at org.jooq.impl.DefaultConverterProvider$2.from(DefaultConverterProvider.java:155)

at org.jooq.impl.AbstractRecord.get(AbstractRecord.java:284)

at org.jooq.impl.DefaultRecordMapper$ValueTypeMapper.map(DefaultRecordMapper.java:605)

at org.jooq.impl.DefaultRecordMapper.map(DefaultRecordMapper.java:548)

I have also tried to stream over the cursor, fetch a Record and then use into(), that gives me the same error as well.

I also tried to fetch the values directly from the Record object, but it looks like that object contains the SELECT query as is (i.e. hierarchy_type ( top_level_bank_id,top_level_account_number, hierarchy_type , 

        CAST ( MULTISET (SELECT hierarchy_account_type( hierarchies.bank_id,hierarchies.account_number,hierarchies.acc_level,

                                                     hierarchies.account_path_ids  ) FROM..) and throws error as below:

java.lang.IllegalArgumentException: Field ("HIERARCHY_BANK_ID") is not contained in Row ("HIERARCHY_TYPE(TOP_LEVEL_BANK_ID,TOP_LEVEL_ACCOUNT_NUMBER,HIERARCHY_TYPE,CAST(MULTISET(SELECTHIERARCHY_ACCOUNT_TYPE(HIERARCHIES.BANK_ID,HIERARCHIES.ACCOUNT_NUMBER,HIERARCHIES.ACC_LEVEL,HIERARCHIES.ACCOUNT_PATH_IDS)FROM(SELECTCAP.TOP_L")

at org.jooq.impl.Tools.indexFail(Tools.java:1998)

where HIREARCHY_BANK_ID is a field defined in HIERARCHY_TYPE

We are using Jooq version 3.14.9

I hope you will be able to help me.

[Lukas Eder]

Hello,

 

It's likely we don't yet support nested collections returned as sys_refcursor contents - skimming our integration tests, I can't see anything like that. I have created an issue to track this problem:

https://github.com/jOOQ/jOOQ/issues/11962

 

I can think of at least two workarounds for the time being:

 

• Declare table types with nested table types and return those instead of a SYS_REFCURSOR (CREATE TYPE out_hierarchy_t AS TABLE OF …)
• Declare the query from your procedure as a view V_OUT_HIERARCHY and return a TABLE OF V_OUT_HIERARCHY%ROWTYPE type (some fixes in this area are on the way for jOOQ 3.15)
• Even returning the nested collection as XML or JSON might help work around the problems, and then mapping the results back to Java classes using Jackson, see https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/

 

The point is that without type information on the SYS_REFCURSOR, we currently cannot map the nested collection, but we’ll look into this via #11962

 

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ed0d3914-7b63-48c6-8c27-62cef60f7c91n%40googlegroups.com.