Oracle中如何移除JSON对象大元素以解决解析过大报错问题

我有一张xxmf_json_feed表，json_data列存储着如下格式的CLOB类型JSON数据：

{
  "P_INVOICE_MASTER_TBL_ITEM": [
    {
      "P_INVOICE_NUM": "INV20250224-1",
      "FILE_NAME": "INV20250224-1.pdf",
      "FILE_CONTENT": "65k_char_blob_content"
    },
    {
      "P_INVOICE_NUM": "INV20250224-2",
      "FILE_NAME": "INV20250224-2.pdf",
      "FILE_CONTENT": "65k_char_blob_content"
    }
  ]
}

其中"65k_char_blob_content"实际是约65000字符的内容。我尝试将P_INVOICE_MASTER_TBL_ITEM数组存入JSON_ARRAY_T变量处理时，触发了"值过大"错误。请问能不能在解析到JSON_ARRAY_T之前先移除FILE_CONTENT元素？

我的PL/SQL代码如下：

DECLARE
  jo JSON_OBJECT_T;
  je JSON_ELEMENT_T;
  ja JSON_ARRAY_T;    
  i PLS_INTEGER := 0;
BEGIN
  FOR r IN (
    SELECT json_data
    FROM   xxmf_json_feed)
  LOOP
    jo := JSON_OBJECT_T.parse(r.json_data);
    je := jo.get('P_INVOICE_MASTER_TBL_ITEM');
    ja := JSON_ARRAY_T.parse(je.to_string); -- line 13 assignment error
    
    LOOP
      je := ja.GET(i);
      EXIT WHEN je IS NULL;
      
      -- process JSON array here
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM: '||SQLERRM);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

报错信息：

SQLERRM: ORA-40478: output value too large (maximum: )
ORA-06512: at "SYS.JDOM_T", line 43
ORA-06512: at "SYS.JSON_ELEMENT_T", line 69
ORA-06512: at line 13
ORA-06512: at line 13

当然可以在解析到JSON_ARRAY_T前移除FILE_CONTENT元素，以下是两种高效的实现方式：

方式一：SQL查询阶段直接过滤冗余字段 #

利用Oracle的JSON_TRANSFORM函数，在查询时就移除FILE_CONTENT元素，拿到精简后的JSON再传入PL/SQL处理，从根源避免大小限制问题：

SELECT JSON_TRANSFORM(
         json_data,
         REMOVE '$.P_INVOICE_MASTER_TBL_ITEM[*].FILE_CONTENT'
       ) AS trimmed_json
FROM xxmf_json_feed;

对应的PL/SQL代码优化：

DECLARE
  jo JSON_OBJECT_T;
  ja JSON_ARRAY_T;    
  i PLS_INTEGER := 0;
  je JSON_ELEMENT_T;
BEGIN
  FOR r IN (
    SELECT JSON_TRANSFORM(
             json_data,
             REMOVE '$.P_INVOICE_MASTER_TBL_ITEM[*].FILE_CONTENT'
           ) AS trimmed_json
    FROM   xxmf_json_feed)
  LOOP
    jo := JSON_OBJECT_T.parse(r.trimmed_json);
    ja := JSON_ARRAY_T(jo.get('P_INVOICE_MASTER_TBL_ITEM')); -- 直接转换，无需转字符串
    
    LOOP
      je := ja.GET(i);
      EXIT WHEN je IS NULL;
      
      -- 示例处理逻辑：提取发票号和文件名
      IF je.is_object THEN
        DECLARE
          jobj JSON_OBJECT_T := JSON_OBJECT_T(je);
        BEGIN
          dbms_output.put_line('发票号: ' || jobj.get_string('P_INVOICE_NUM'));
          dbms_output.put_line('文件名: ' || jobj.get_string('FILE_NAME'));
        END;
      END IF;
      
      i := i + 1;
    END LOOP;
    i := 0; -- 重置计数器，处理下一行数据
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM: '||SQLERRM);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

方式二：PL/SQL中直接操作JSON对象移除字段 #

如果不想修改查询语句，可在PL/SQL里遍历数组元素，逐个移除FILE_CONTENT：

DECLARE
  jo JSON_OBJECT_T;
  ja JSON_ARRAY_T;    
  i PLS_INTEGER := 0;
  je JSON_ELEMENT_T;
BEGIN
  FOR r IN (
    SELECT json_data
    FROM   xxmf_json_feed)
  LOOP
    jo := JSON_OBJECT_T.parse(r.json_data);
    ja := JSON_ARRAY_T(jo.get('P_INVOICE_MASTER_TBL_ITEM')); -- 直接转换，避免to_string的大小限制
    
    -- 遍历数组移除冗余字段并处理
    WHILE i < ja.get_size LOOP
      je := ja.get(i);
      IF je.is_object THEN
        DECLARE
          jobj JSON_OBJECT_T := JSON_OBJECT_T(je);
        BEGIN
          jobj.remove('FILE_CONTENT'); -- 移除FILE_CONTENT字段
          -- 示例处理逻辑
          dbms_output.put_line('发票号: ' || jobj.get_string('P_INVOICE_NUM'));
        END;
      END IF;
      i := i + 1;
    END LOOP;
    i := 0; -- 重置计数器
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM: '||SQLERRM);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

核心优化说明 #

• 避免调用je.to_string()：原代码报错的核心原因是FILE_CONTENT内容过长，调用to_string()时超出了函数的字符限制。直接将JSON_ELEMENT_T强制转换为JSON_ARRAY_T（JSON_ARRAY_T(je)）可绕过这个问题。
• 优先SQL层处理：Oracle的JSON原生函数处理大JSON数据的效率更高，能减少PL/SQL层的内存占用。

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