MyBatis Support of nested cursors

Hello! Can myBatis support oracle nested cursors ? https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions005.htm

for example i have next select statement:

<select id="tt" statementType="PREPARED" resultType="hashmap">
SELECT 1 id
                ,CURSOR (SELECT LEVEL
                    FROM dual
                    CONNECT BY LEVEL &gt;= 5) coll
        FROM dual
</select>

and take a result = {COLL=oracle.jdbc.driver.OracleResultSetImpl@54c75937, ID=1}

please add support of evaluating nested cursors

Comment From: albertKrafter

Hi! Any news about this request? @dsulimchuk did you find any alternative?

Comment From: harawata

Could somebody show me how this works in JDBC? I mean...

String sql = "select 1 id, cursor(select level from dual connect by level >= 5) coll from dual";
try (Connection con = new JdbcConnection("oracle18").getConnection();
    PreparedStatement stmt = con.prepareStatement(sql);
    ResultSet rs = stmt.executeQuery()) {
  while (rs.next()) {
    int id = rs.getInt("id");
    // How to get the 'coll' value?
  }
}

Comment From: albertKrafter

...
while (rs.next()) {
    int id = rs.getInt("id");
    // How to get the 'coll' value?
    ResultSet nestedRs = (ResultSet)rs.getObject("coll");
  }
...

Comment From: harawata

Okay. Then you may be able to write a type handler as a workaround.

<resultMap type="map" id="rm">
  <id column="id" property="id" javaType="int" />
  <result column="coll" property="coll" typeHandler="test.CursorTypeHandler" />
</resultMap>

<select id="selectNestedCursor" resultMap="rm">
  select 1 id,
  cursor(select level from dual connect by level >= 5) coll from
  dual
</select>

public class CursorTypeHandler extends BaseTypeHandler<Map<String, Object>>{

  @Override
  public Map<String, Object> getNullableResult(ResultSet rs, String columnName) throws SQLException {
    Map<String, Object> result = new HashMap<>();
    try (ResultSet nestedRs = rs.getObject(columnName, ResultSet.class)){
      ResultSetMetaData rsmd = nestedRs.getMetaData();
      for (int i = 0; i < rsmd.getColumnCount(); i++) {
        String key = rsmd.getColumnName(i + 1);
        Object value = rs.getObject(i + 1);
        result.put(key, value);
      }
    }
    return result;
  }

It might be nice if we could reuse an existing result map.

Comment From: albertKrafter

This is the problem. A TypeHandler cannot access the mappers, so it cannot invoke a properly configured ResultSetHandler on "value".

Comment From: albertKrafter

It should be best if the "/mapper/resultMap/result" element could contain nested resultmaps linked to columns, e. g.: <result column="MY_NESTED_CURSOR" property="listOfMyNestedThings" jdbcType="CURSOR" javaType="java.util.List" resultMap="MyNestedThingResult" /> where "listOfMyNestedThings" is of type "List\<MyNestedThing>" and is a property of the object which "owns" the main "resultMap".

Comment From: albertKrafter

@harawata Hi! Thank you for the implementation! I'll try it as soon as possible!

Comment From: harawata

Hello @albertKrafter ,

That would be really helpful, thank you!

You can try it with the latest 3.6.0-SNAPSHOT. There are some different usage patterns in the test.