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 jsonFROM 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 jsonFROM tablename;

XMLAGG handles CLOBs 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

By default the new json_* functions return a varchar2(4000). You can change this in the returning clause.

If you have extended data types enabled, you can change this to a varchar2(32767). But only the *agg functions support clob.

from here

SELECT length(JSON_ARRAYAGG(          JSON_OBJECT(            KEY 'object_type' VALUE object_type,            KEY 'object_name' VALUE object_name          )        returning clob)        ) array_sizeFROM   all_objects;ARRAY_SIZE  5772072  

18c also has full support for clobs in the JSON* functions

In 12.2 json_* functions handle clobs fine. Use clause Returning clob

create table t( c clob, constraint t_chk check (c is json));declare    v_clob clob;begin    for i in 1..10000 loop        v_clob := v_clob || 'asdasdadasdasdasdasdasdasdasd';    end loop;    insert into t(c)     select         json_object        (           'body' value v_clob returning clob         )    from        dual;end;