Friday 27 January 2017

Truncating LISTAGG prior to 12.2

I've been following the LISTAGG problem for a while because I find the function useful.

The problem being this error:
ORA-01489: result of string concatenation is too long

It relates to when the result set is over 4000 characters, eg:
with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(str,', ') within group (order by null)
from data
Ten rows of 400 characters equals 4000, add the concatenation operator and you get the error.
If the second parameter was null, this would actually be within the 4000 limit.

There are improvements in 12.2 to handle this overflow.
with data as (select rpad('x',400, 'x') str from dual connect by level <=11)
select listagg(str, ',' on overflow truncate '...' with count) within group (order by null)
from data

The output would end
...*********,...(8985)

But if you're still in 12.1 or earlier, you need other options

One solution is a custom LISTAGG function to return a CLOB.

You could make a decision on the incoming length, which probably isn't always practical, or you can concatenate only the first x rows using a CASE statement
with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(case when rownum < 5 then str end
              ,', ') within group (order by null)
            ||'First 5 of '||count(*)||' shown.' as str
from data;

Which ends the output with
...********First 5 of 10 shown.

I suspect this will do for a lot of scenarios. Ordering is up to you.

I don't remember encountering this idea in the context of LISTAGG, but it seems nice and simple.

LiveSQL example:
https://livesql.oracle.com/apex/livesql/file/content_EHQAVVYH6J2XMN5VHTFJYT3Y8.html

No comments: