How to generate JSON in Oracle for a CLOB that is > 32k (e.g. 60,000 characters)?
In answer to this question:
3 approach solve my problem but i don't want to run for loop . Is there is any solution in oracle to handle this .
Strings can be concatenated without looping by using Oracle's LISTAGG
function:
SELECT '{"employees":[' || LISTAGG('{"employee_id":' || to_char(employee_id)
|| ',"data_clob":"' || data_clob || '"}', ',')
WITHIN GROUP (ORDER BY employee_id) || ']}' AS json
FROM tablename;
However, as you've pointed out in the comments, LISTAGG
has a limit of 4000 characters. The following is more complex/fiddly but should cope beyond this limit:
SELECT '{"employees":[' || dbms_xmlgen.convert(
RTRIM(XMLAGG(XMLELEMENT(E,'{"employee_id":' || to_char(employee_id)
|| ',"data_clob":"' || data_clob || '"}',',')
.EXTRACT('//text()') ORDER BY employee_id).GetClobVal(),',')
, 1) || ']}' AS json
FROM tablename;
XMLAGG
handles CLOB
s but the EXTRACT
function has the side-effect of escaping certain characters (e.g. from "
to "
). The query above converts these back (e.g. from "
to "
) using the dbms_xmlgen.convert
function - see this answer for further details.
SQL Fiddle demo: http://sqlfiddle.com/#!4/5b295/40