Oracle Apex遍历嵌套JSON元素报错及优化需求

一、ORA-40441错误排查（JSON已验证有效时） #

即使在线工具验证JSON有效，Oracle 21c的JSON解析器默认采用严格模式，仍可能触发该错误，常见原因及排查步骤：

• 尾随逗号/非标准键名：Oracle不允许JSON对象/数组末尾的逗号，也要求键名必须用双引号包裹（部分在线工具允许无引号键名）
• 字符集不兼容：如果CLOB采用单字节字符集（如US7ASCII），JSON中的非ASCII字符会导致解析失败
• 控制字符未转义：JSON中的换行、制表符等控制字符必须转义，在线工具可能忽略这类问题
• 隐式转换问题：直接传递CLOB给JSON函数时，需确保Oracle能识别为有效JSON，可通过以下SQL验证：

-- 检查JSON有效性
SELECT JSON_VALID(your_clob_column) FROM your_table;

-- 尝试用宽松模式解析
SELECT JSON_VALUE(your_clob_column, '$.target_key' FORMAT JSON LOOSE) FROM your_table;

二、嵌套JSON遍历的替代方案（无需自定义存储过程） #

基于Oracle XE21c和APEX 22.2的原生工具，推荐两种高效实现方式：

1. 使用APEX_JSON包（适配APEX环境） #

APEX_JSON是Oracle官方为APEX环境提供的JSON处理工具，内置递归遍历支持，示例代码：

DECLARE
    l_clob CLOB := '{"root": {"level1": 1, "level2": {"level3": ["a", "b", {"level4": 2}]}}}';
    l_json apex_json.t_values;

    PROCEDURE traverse(p_path VARCHAR2, p_data apex_json.t_values, p_index PLS_INTEGER) IS
        l_type apex_json.t_type;
        l_count PLS_INTEGER;
    BEGIN
        l_type := apex_json.get_type(p_data, p_index);
        -- 处理对象类型
        IF l_type = apex_json.t_type_object THEN
            l_count := apex_json.get_count(p_data, p_index);
            FOR i IN 1..l_count LOOP
                traverse(
                    p_path || '.' || apex_json.get_name(p_data, p_index, i),
                    p_data,
                    apex_json.get_index(p_data, p_index, i)
                );
            END LOOP;
        -- 处理数组类型
        ELSIF l_type = apex_json.t_type_array THEN
            l_count := apex_json.get_count(p_data, p_index);
            FOR i IN 1..l_count LOOP
                traverse(
                    p_path || '[' || i || ']',
                    p_data,
                    apex_json.get_index(p_data, p_index, i)
                );
            END LOOP;
        -- 处理基础类型（字符串、数字等）
        ELSE
            DBMS_OUTPUT.PUT_LINE(p_path || ' = ' || apex_json.get_varchar2(p_data, p_index));
        END IF;
    END;
BEGIN
    apex_json.parse(l_json, l_clob);
    traverse('', l_json, 1);
END;
/

2. 使用SQL递归查询（JSON_TABLE） #

适合直接通过SQL输出所有嵌套元素的路径和值，示例：

WITH RECURSIVE json_tree AS (
    -- 初始节点：根JSON对象/数组
    SELECT
        '$' AS element_path,
        jt.value_type,
        jt.value_data
    FROM your_table t,
         JSON_TABLE(
             t.your_clob_column, '$'
             COLUMNS (
                 value_type VARCHAR2(10) PATH '$.type()',
                 value_data CLOB PATH '$'
             )
         ) jt
    UNION ALL
    -- 递归遍历子节点
    SELECT
        CASE
            WHEN parent.value_type = 'object' THEN parent.element_path || '.' || jt.key_name
            WHEN parent.value_type = 'array' THEN parent.element_path || '[' || jt.array_idx || ']'
        END AS element_path,
        jt.child_type,
        jt.child_data
    FROM json_tree parent,
         JSON_TABLE(
             parent.value_data,
             CASE parent.value_type
                 WHEN 'object' THEN '$.*'
                 WHEN 'array' THEN '$[*]'
             END
             COLUMNS (
                 key_name VARCHAR2(100) PATH '$.key()' ERROR ON ERROR NULL ON EMPTY,
                 array_idx FOR ORDINALITY,
                 child_type VARCHAR2(10) PATH '$.type()',
                 child_data CLOB PATH '$'
             )
         ) jt
)
SELECT element_path, value_type, child_data FROM json_tree;

三、自定义存储过程优化要点 #

若坚持使用自定义PL/SQL存储过程，需注意：

• 避免手动字符串解析，改用Oracle原生的JSON_OBJECT_T/JSON_ARRAY_T API处理，减少语法错误
• 完善递归逻辑，明确区分对象、数组、基础类型的处理分支
• 添加异常捕获，输出当前遍历路径和错误信息，便于定位问题：

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Traversal failed at path: ' || v_current_path || ' | Error: ' || SQLERRM);

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