ORA-40478 when returning large json into clob in Oracle 19c
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;
The character string returned by this function is of data type VARCHAR2. This clause allows you to specify the size of the VARCHAR2 data type. Use BYTE to specify the size as a number of bytes or CHAR to specify the size as a number of characters. The default is BYTE. If you omit this clause, or if you specify this clause but omit the size value, then JSON_OBJECT returns a character string of type VARCHAR2(4000).