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

user_1194399 0
Viewed 9267

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)

clob oracle json
3 Answers

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

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": []
  }
]
user_7427550

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

user_13328700
Related