Oracle APEX服务器端REST API POST获取JSON数据并执行更新报错求助

核心实现思路 #

1. 完整解析POST请求体的JSON数据，重点处理嵌套的mData对象，将其键（L/B/D）映射为t1表的m_type字段，对应值映射为count字段。
2. 将传入的ISO格式日期字符串转换为Oracle DATE类型，匹配t1表的day字段类型。
3. 遍历mData的键值对执行更新操作，同时添加异常处理避免返回555错误。

完整PL/SQL代码实现 #

DECLARE
    l_json_obj apex_json.t_values;
    l_mdata_obj apex_json.t_values;
    l_day DATE;
    l_m_type VARCHAR2(10);
    l_count NUMBER;
BEGIN
    -- 解析请求体JSON
    apex_json.parse(l_json_obj, :body);

    -- 转换ISO日期字符串为Oracle DATE类型
    l_day := TO_DATE(apex_json.get_varchar2(p_path => 'day', p_values => l_json_obj), 
                     'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"');

    -- 提取嵌套的mData对象
    apex_json.get_object(p_path => 'mData', p_values => l_json_obj, p_result => l_mdata_obj);

    -- 遍历mData的所有键值对执行更新
    FOR i IN 1..apex_json.get_count(p_values => l_mdata_obj) LOOP
        l_m_type := apex_json.get_name(p_pos => i, p_values => l_mdata_obj);
        l_count := apex_json.get_number(p_pos => i, p_values => l_mdata_obj);

        UPDATE t1
        SET count = l_count, day = l_day
        WHERE m_type = l_m_type;
    END LOOP;

    COMMIT;

    -- 返回成功响应
    apex_json.open_object;
    apex_json.write('status', 'success');
    apex_json.write('message', '更新完成');
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常并返回明确错误信息
        apex_json.open_object;
        apex_json.write('status', 'error');
        apex_json.write('message', SQLERRM);
        apex_json.close_object;
        ROLLBACK;
END;

关键说明 #

• JSON解析：通过apex_json.parse解析整个请求体，用apex_json.get_object提取嵌套的mData，再通过get_name和get_number遍历键值对。
• 日期转换：针对带Z后缀的ISO格式日期，使用TO_DATE配合格式掩码'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'完成类型转换。
• 异常处理：添加异常捕获块，返回结构化JSON错误响应，避免APEX返回模糊的555内部错误码。
• 绑定变量：确保REST服务处理程序中已配置请求体绑定变量为:body，若无法获取可替换为apex_web_service.g_request_body。

替代方案：SQL方式解析JSON #

如果偏好SQL语法，可使用JSON_TABLE将mData转为行集，通过MERGE批量更新：

DECLARE
    l_day DATE;
BEGIN
    l_day := TO_DATE(apex_json.get_varchar2(p_path => 'day', p_source => :body), 
                     'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"');

    MERGE INTO t1 t
    USING (
        SELECT 
            j.m_type,
            j.count_val
        FROM JSON_TABLE(
            :body,
            '$.mData.*' COLUMNS (
                m_type VARCHAR2(10) PATH '$key',
                count_val NUMBER PATH '$value'
            )
        ) j
    ) src
    ON (t.m_type = src.m_type)
    WHEN MATCHED THEN
        UPDATE SET t.count = src.count_val, t.day = l_day;

    COMMIT;

    apex_json.open_object;
    apex_json.write('status', 'success');
    apex_json.write('message', '更新完成');
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('status', 'error');
        apex_json.write('message', SQLERRM);
        apex_json.close_object;
        ROLLBACK;
END;

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