Wednesday 4 March 2015

LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

Update 2019: Connor now his this code in his Github.

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search after the fact.

A little tip if you do find it - don't attempt to copy the code from the translated version of the page. It's amazing how many syntax issues were introduced by the translator, and logical issues that I wouldn't have noticed had I not performed a diff after things went wrong.

For instance, the "aggregate" keyword here vanished:
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/

Thank's Carsten for a useful solution using custom aggregate functions, an element of PL/SQL I'm yet to master.

It's certainly faster than the XML solution, here is the throughput difference from 100 iterations of each.
-- listagg_clob
3.34 secs 
.0334 secs per iteration
-- xmlagg
18.75 secs 
.1875 secs per iteration

The simplest solution, however, would be to set up an RESTful service using ORDS.

Another workaround involves a CASE statement.

3 comments:

Anonymous said...

See also this post for another solution: https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/

Anton

Scott Wesley said...

Nice, I'll try read that again later and see if I can better comprehend these aggregation functions!

Anonymous said...

It works very fast an efficient BUT (unless I missed something):
1. Misses an option to sort the output (even an ORDER BY in the query itself does not cause the output to be sorted)
2. Misses an option to change the separator (unless changing and recompiling the code before the run)