ORA-40478 when returning large json into clob in Oracle 19c

Question

In Oracle 19c I created the table:

create table SAMPLE_TABLE (
id NUMBER not null,
display_name  NVARCHAR2(4000), )

When I run the script:

declare    
   i integer;   
   p_tmp_clob clob;
 begin   
   select JSON_ARRAYAGG(JSON_OBJECT(t.* )) into p_tmp_clob from SAMPLE_TABLE t;
end; 

I am getting the following ORA-40478 exception. ORA-40478: output value too large (maximum: 4000)

Answer 1

You need to tell the function to return a CLOB, not a varchar:

It might be necessary for JSON_ARRAYAGG as well (or maybe only there - I can't test it right now)

declare    
   i integer;   
   p_tmp_clob clob;
 begin   
   select JSON_ARRAYAGG(JSON_OBJECT(t.* RETURNING CLOB) RETURNING CLOB) 
     into p_tmp_clob 
   from SAMPLE_TABLE t;
end;