PL/SQL生成JSON_OBJECT_T遇长度超限错误的解决请求

错误原因 #

ORA-40478错误的核心问题是：你外层的JSON_OBJECT没有显式指定返回CLOB类型。虽然内层JSON_ARRAYAGG加了RETURNING CLOB，但外层JSON_OBJECT默认返回VARCHAR2类型，其最大长度不足以容纳30条员工记录生成的JSON内容，因此触发了"output value too large"错误。

解决方案 #

方案1：给外层JSON_OBJECT添加RETURNING CLOB #

修改原代码，在最外层的JSON_OBJECT末尾加上RETURNING CLOB，确保整个JSON结果以CLOB类型返回，避免长度限制：

DECLARE
  l_json_object JSON_OBJECT_T;
  l_json CLOB;
BEGIN
   SELECT
      JSON_OBJECT(
         KEY 'employees' VALUE 
            JSON_ARRAYAGG(
               JSON_OBJECT(KEY 'empno' VALUE id
                           ,KEY 'ename' VALUE name
                           ,KEY 'job' VALUE email_id)
               ORDER BY id) 
            RETURNING CLOB)
      RETURNING CLOB  -- 显式指定外层返回CLOB
     INTO l_json
     FROM employee_tbl;

   l_json_object := JSON_OBJECT_T.PARSE(l_json);

   dbms_output.put_line(l_json_object.to_clob);
END;
/

方案2：直接构造JSON_OBJECT_T（更高效） #

跳过中间的CLOB变量，直接从查询结果构造JSON_OBJECT_T，减少一次解析步骤：

DECLARE
  l_json_object JSON_OBJECT_T;
BEGIN
   SELECT
      JSON_OBJECT_T(
         JSON_OBJECT(
            KEY 'employees' VALUE 
               JSON_ARRAYAGG(
                  JSON_OBJECT(KEY 'empno' VALUE id
                              ,KEY 'ename' VALUE name
                              ,KEY 'job' VALUE email_id)
                  ORDER BY id) 
               RETURNING CLOB)
         RETURNING CLOB)
     INTO l_json_object
     FROM employee_tbl;

   dbms_output.put_line(l_json_object.to_clob);
END;
/

验证结果 #

执行修正后的代码，会生成符合预期的JSON结构：

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

内容的提问来源于stack exchange，提问作者mu shaikh