How to generate JSON in Oracle for a CLOB that is > 32k (e.g. 60,000 characters)?

Question

1 ) I have to make json from oracle select query which has three approach i can follow .

SELECT JSON_ARRAY(json_object('id'         VALUE employee_id, 
                   'data_clob'    VALUE data_clob
                     )) from tablename;

also i have tried with this approach

2) If you are unable to patch/work with that version there is an excellent package written by Lewis Cunningham and Jonas Krogsboell: PL/JSON * http://pljson.sourceforge.net/

It's an excellent package (I have used it in numerous database installations).

The examples included are good and cover most scenarios.

declare 
  ret json;
begin
  ret := json_dyn.executeObject('select * from tab');
  ret.print;
end;
/

Mention In this answer too but not work for such big clob. Return results of a sql query as JSON in oracle 12c

3) The other approach can be we can concatenate the string after the select query.

FOR rec IN (SELECT employee_id, data_clob
                FROM tablename) LOOP
      IF i <> 1 THEN
        v_result := v_result || ',';
      END IF;

      v_result := v_result || '{"employee_id":' || to_char(rec.employee_id) || ',"data_clob": ' || rec.data_clob || '}';

      i := i + 1;
    END LOOP;
    v_result := v_result || ']}'; 

3 approach solve my problem but i don't want to run for loop . Is there is any solution in oracle to handle this .

I check for solution but that don't work without for loop.

https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/

url has provide some solution , i tried this but not working .Same issue is coming.

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 57416, maximum: 4000)

Could you able to tell me how it can be done ?

Answer 1

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