JOOQ DSL way of working around ORA-01489 in LISTAGG()

Question

In Oracle SQL there is this workaround using XMLAGG instead of LISTAGG when the resulting text would be too large for VARCHAR2 (error message: ORA-01489 Result of string concat is too large).

SQL example:

LISTAGG(MY_TEXT_SNIPPET) WITHIN GROUP (order by SNIPPET_NO)

workaround

RTRIM(XMLAGG(XMLELEMENT(e, MY_TEXT_SNIPPET,'').EXTRACT('//text()') ORDER BY SNIPPET_NO).GetClobVal(),',')

How would you do the latter with JOOQ?

Answer 1

Some of the XML functions are supported starting from jOOQ 3.14, including:

• XMLAGG
• XMLELEMENT

But neither EXTRACT() nor GetClobVal() are supported yet in this form. You try using XMLQUERY() and casting to CLOB instead.

Alternatively, as always when a vendor-specific feature isn't supported, you can resort to using plain SQL templating:

Mixing jOOQ API with templates

public static Field<String> listAggWorkaround(Field<?> field, Field<?> orderBy) {
  return rtrim(
    field("{0}.GetClobVal()", SQLDataType.CLOB, 
      xmlagg(field("{0}.extract('//text()')", SQLDataType.XML, 
        xmlelement("e", field, inline(""))
      )).orderBy(orderBy)
    ),
    inline(",")
  );
}

Using only templates

public static Field<String> listAggWorkaround(Field<?> field, Field<?> orderBy) {
  return field(
    "rtrim(xmlagg(xmlelement(e,{0},'').extract('//text()') order by {1}).GetClobVal(),',')", 
    SQLDataType.CLOB,
    field, orderBy
  );
}