Oracle 12c自定义函数返回JSON_OBJECT_LIST问题求助

1. 返回值构造与类型匹配问题 #

• 确认JSON_OBJECT_LIST的初始化是否符合内部JSON_UTIL库规范，比如是否需要显式调用JSON_OBJECT_LIST()创建空列表，而非直接赋值。
• 检查单条JSON_OBJECT的构造逻辑：必须用JSON_UTIL提供的构造方法封装字段，比如JSON_OBJECT('BRKPK_CNTNR_ID', rec.BRKPK_CNTNR_ID, 'ITEM_DISTB_Q', rec.ITEM_DISTB_Q)，要保证字段名拼写、数据类型和表中字段完全匹配，避免隐式转换报错。

2. 游标与循环逻辑问题 #

• 对比能正常运行的匿名块，核对函数内的游标查询语句：确保SELECT BRKPK_CNTNR_ID, ITEM_DISTB_Q FROM OVRPK_DET WHERE OVRPK_CNTNR_ID = p_input_id的条件、字段和匿名块完全一致，没有字段名大小写、拼写错误。
• 循环内必须执行列表添加操作：比如调用v_json_list.append(json_obj)，如果遗漏这一步，会导致最终返回空列表。
• 优先用CURSOR FOR LOOP自动管理游标，避免手动打开/关闭游标时出现资源泄漏或未关闭的问题；如果手动处理游标，要在异常块中添加关闭逻辑。

3. 异常处理缺失问题 #

• 函数必须添加异常捕获逻辑：比如查询无数据时，按JSON_UTIL要求返回空列表或自定义提示；如果ITEM_DISTB_Q是数值型，要确认JSON_UTIL是否支持直接传入数值，不支持的话需用TO_CHAR(ITEM_DISTB_Q)转换为字符串后再构造JSON。
• 避免未处理异常导致函数崩溃，可在EXCEPTION块中捕获NO_DATA_FOUND或OTHERS异常，返回合法的空列表。

4. 参数传递问题 #

• 确认函数参数OVRPK_CNTNR_ID的类型和表中字段完全一致：比如表中是VARCHAR2(100)，函数参数就不能定义为NUMBER，否则会因类型转换导致查询无结果。
• 调用函数时要检查传入参数是否存在前后空格，这类隐性问题会导致匹配失败。

修正后的函数示例框架 #

CREATE OR REPLACE FUNCTION get_json_objects(p_ovrpk_cntnr_id OVRPK_DET.OVRPK_CNTNR_ID%TYPE)
RETURN JSON_OBJECT_LIST
IS
    v_json_list JSON_OBJECT_LIST := JSON_OBJECT_LIST();
    CURSOR c_target_data IS
        SELECT BRKPK_CNTNR_ID, ITEM_DISTB_Q
        FROM OVRPK_DET
        WHERE OVRPK_CNTNR_ID = p_ovrpk_cntnr_id;
BEGIN
    FOR rec IN c_target_data LOOP
        v_json_list.append(
            JSON_OBJECT(
                'BRKPK_CNTNR_ID', rec.BRKPK_CNTNR_ID,
                'ITEM_DISTB_Q', rec.ITEM_DISTB_Q
            )
        );
    END LOOP;
    RETURN v_json_list;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN JSON_OBJECT_LIST();
    WHEN OTHERS THEN
        -- 可根据需求添加日志记录，这里返回空列表保证函数合法性
        RETURN JSON_OBJECT_LIST();
END;
/

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