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 dataTen 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:
Post a Comment