Oracle中使用json_arrayagg(json_object(*))序列化大查询报ORA-40478如何解决

使用如下SQL可实现查询结果的JSON序列化：

WITH
    bigquery
    AS
        (SELECT level from dual connect by level<1000)
SELECT json_arrayagg(json_object(*))
  FROM bigquery

当查询数据量较大时，上述SQL执行失败，抛出错误：

ORA-40478: 输出值过大（上限：4000）

经对比验证，直接查询大结果集的SQL可正常运行，代码如下：

WITH
    bigquery
    AS
        (SELECT level from dual connect by level<1000)
SELECT *
  FROM bigquery

确认报错由json_arrayagg(json_object(*))触发：该函数默认返回VARCHAR2类型结果，受SQL层面VARCHAR2最大4000字节的长度限制，大结果集生成的JSON串长度超出阈值就会触发报错。
该问题可在Oracle 18c环境下稳定复现。

在JSON聚合函数中显式指定返回大字段类型即可突破长度限制，修正后代码如下：

WITH
    bigquery
    AS
        (SELECT level from dual connect by level<1000)
SELECT json_arrayagg(json_object(*) RETURNING CLOB)
  FROM bigquery

方案说明：

• 增加RETURNING CLOB声明后，聚合生成的JSON结果将以CLOB类型返回，支持最大4GB的文本长度，可满足绝大多数大结果集的序列化导出需求。
• 若使用Oracle 21c及以上版本，可替换为RETURNING JSON声明，使用原生JSON类型存储返回结果，长度上限更高，JSON解析与查询性能也更优。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud