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)

score 22 · Accepted Answer · answered Oct 11 '19 at 12:02

22

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;

answered Oct 11 '19 at 12:02

Yes this is correct-- confirming it worked for me. Additionally, no you don't need `returning clob` on the inner `json_object` unless you do :) (if you go over default `varchar2(4000)`), but I also wonder what happens if you try to cast a clob to a clob? Note: you'll possibly want to `COALESCE(, TO_CLOB('[]'])` if you, like me, want an empty array in the event of no results from that inner select query. – texas-bronius Jun 16 '22 at 22:38
One more gem for you: append `format json` to every _value_ you don't want to be turned into a string. Oh dang - I got limited by Comment. Lemme add another answer to exemplify my point.. – texas-bronius Jun 17 '22 at 22:00

score 2 · Answer 2 · answered Jun 17 '22 at 22:12

To supplement my suggestions in a comment response to @a_horse_with_no_name's correct answer above:

In the end, in all, you need

a combo of COALESCE
with a clob-compatible fallback of json array
and RETURNING CLOB in each json_agg* function (to be safe: it's only needed on the one wrapping content larger than varchar2(4000) and any json_agg* function wrapping it (ie: both inner object and outer array)
on each thing that could return an empty resultset

Example:

SELECT
  coalesce(
    json_arrayagg(
      json_object(
        'something' value 'Something',
        'staticEmptyArrayString' value to_clob('[]'),
        'staticEmptyArrayReally' value to_clob('[]') format json,
        'reallyEmptyArrayNull' value (SELECT to_clob('') FROM dual),
        'reallyEmptyArray' value COALESCE((SELECT to_clob('') FROM dual), to_clob('[]')) format json,
        'reallyEmptyArrayToo' value COALESCE((SELECT to_clob('') FROM dual), to_clob('[]')) format json,
        'reallyEmptyArrayAlso' value COALESCE((SELECT to_clob('') FROM dual), to_clob('[]'))
        format json
        returning clob
      )
      returning clob
    ),
    to_clob('[]')
  ) AS json_out
  FROM dual;

begets:

[
  {
    "something": "Something",
    "staticEmptyArrayString": "[]",
    "staticEmptyArrayReally": [],
    "reallyEmptyArrayNull": null,
    "reallyEmptyArray": [],
    "reallyEmptyArrayToo": [],
    "reallyEmptyArrayAlso": []
  }
]

I could not figure out how to slide in something that is actually a nested array of values in my static, dual-using examples: `'realArray' value json_arrayagg((SELECT 'abc' FROM dual UNION SELECT '123' FROM dual)) format json,` didn't do it. — texas-bronius, Jun 17 '22 at 22:13

score 0 · Answer 3 · answered Oct 11 '19 at 11:59

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

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

3 Answers3