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

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.

2 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!