Oracle 19C读取10k+表记录生成指定JSON并返回CLOB求助

针对10k+条数据的场景，直接使用Oracle 19C原生JSON函数是最高效且避免错误的方案，无需循环处理，以下是具体实现：

最优存储过程实现 #

CREATE OR REPLACE PROCEDURE GET_CODECLASS_JSON(p_result OUT CLOB)
IS
BEGIN
  SELECT JSON_OBJECT(
           'CODECLASS' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               'FCODES' VALUE FCODES,
               'CLASSFL' VALUE CLASSFL
             ) FORMAT JSON
           ) FORMAT JSON
         ) INTO p_result
  FROM CODES_CLASS;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/

方案说明 #

• 利用JSON_OBJECT生成外层结构，JSON_ARRAYAGG批量生成数组内的对象集合，19C版本中通过FORMAT JSON参数可直接让聚合函数返回CLOB类型，避免VARCHAR2长度限制。
• 无需循环或批量收集，原生SQL引擎直接处理数据生成完整JSON，既保证格式正确，又提升性能。

之前问题的原因分析 #

1. 循环处理JSON_OBJECT：手动循环拼接CLOB若未正确使用DBMS_LOB.APPEND，易出现截断、格式混乱或性能瓶颈。
2. JSON_ARRAYAGG后循环：完全冗余操作，JSON_ARRAYAGG本身已生成完整数组结构，循环会破坏JSON格式。
3. APEX_JSON循环的ORA-06502：10k+数据量下，APEX_JSON的内存处理机制易因变量长度不足或内存溢出触发数值/值错误，原生JSON函数更适配大数据量场景。

注意事项 #

• 若字段包含双引号、反斜杠等特殊字符，JSON_OBJECT会自动转义，无需手动处理。
• 测试时可先单独执行SELECT语句验证JSON结构是否符合需求，再封装到存储过程。

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