STRAGG for CLOB?

Question

Is there some function that makes the same behavior of SYS.STRAGG in oracle but instead of returning VARCHAR (and being limited to the VARCHAR size), it returns a CLOB , and thus allows (virtually) infinite number of concatenated strings ?

for example , I have a query select x from y where z that returns 2.5M records and I want to return all these records concatenated together 1 shot

Always mention your **database version** up to four decimal places. — Lalit Kumar B, Mar 09 '16 at 09:54
Possible duplicate of [LISTAGG function: “result of string concatenation is too long”](http://stackoverflow.com/q/13795220/266304)? `stragg` is undocumented so you should be using `listagg` anyway, for shorter values. — Alex Poole, Mar 09 '16 at 09:55

score 1 · Answer 1 · answered Mar 09 '16 at 09:52

XML functions can be used for such aggregation but for 2.5M records it will be very slow.

Example:

SELECT
    rtrim(
        dbms_xmlgen.convert(
            extract(
                xmlroot(
                    xmlelement(
                        "x",
                        xmlagg(sys_xmlgen(object_name || ', '))
                    ),
                version '1.0'),
            '/x/ROW/text()').getclobval(),
        1),
    ', ') aggregated_data
FROM
    all_objects

You might consider to use LISTAGG for pre-aggregation of small row groups into VARCHARs smaller than 4000/32767 bytes and then use the XML aggregation for the final result.

STRAGG for CLOB?

1 Answers1