ORA-01422错误排查：多行数据转JSON返回前端的问题求助

错误原因 #

ORA-01422 是因为你的 SELECT INTO 语句期望返回单行结果，但指定ID匹配到了多条记录，导致查询返回多行，无法直接赋值给单个变量。同时原代码处理BLOB字段的方式存在隐患，JSON无法直接存储二进制数据，需要转成可序列化的格式。

修正后的代码 #

DECLARE
    v_json_result_clob CLOB;
BEGIN
    -- 使用JSON_ARRAYAGG聚合多行记录为单个JSON数组，解决多行返回问题
    -- 将BLOB转成BASE64编码字符串，确保JSON可序列化
    SELECT NVL(
        JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'id'           VALUE ID,
                KEY 'blob_data'    VALUE UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(IMAGE))
            ) RETURNING CLOB
        ),
        JSON_ARRAY() RETURNING CLOB -- 无匹配记录时返回空数组
    ) INTO v_json_result_clob 
    FROM TEST_TABLE 
    WHERE ID = apex_application.g_x01;

    -- 构建响应JSON，使用write_raw避免JSON数组被转义为字符串
    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write_raw('result', v_json_result_clob);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

关键修改点说明 #

1. 替换JSON_ARRAY为JSON_ARRAYAGG：
   JSON_ARRAYAGG 会将所有匹配行的JSON_OBJECT聚合为一个JSON数组，让整个查询返回单行结果，彻底解决ORA-01422错误。

2. BLOB字段转BASE64编码：
   使用 UTL_ENCODE.BASE64_ENCODE 将BLOB转成RAW类型，再通过 UTL_RAW.CAST_TO_VARCHAR2 转成字符串存入JSON。前端JavaScript可以通过 atob() 解码该字符串，再转换为Blob对象使用。如果BLOB体积较大，可替换为 DBMS_LOB.CONVERT_TO_CLOB(UTL_ENCODE.BASE64_ENCODE(IMAGE)) 确保不会超出字符长度限制。

3. 使用apex_json.write_raw写入结果：
   原代码的 apex_json.write 会将CLOB内容视为普通字符串，添加引号并转义特殊字符，导致前端拿到的result是字符串而非数组。write_raw 会直接写入原始JSON内容，保证响应的JSON结构正确。

4. NVL处理无匹配记录场景：
   当没有匹配到指定ID的记录时，JSON_ARRAYAGG会返回NULL，通过NVL替换为空数组 []，让前端得到更统一的响应格式。

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