Oracle 12.1 JSON_TABLE查询报ORA-40441错误无法加载全部JSON记录

一、定位出错记录 #

你当前代码无法捕获错误的核心原因是：ORA-40441属于JSON解析阶段抛出的错误，发生在游标遍历JSON_TABLE结果的环节，还没进入循环内的异常捕获逻辑，所以错误直接中断了整个流程，无法定位到具体问题行。

可以用以下两种方法快速定位错误：

• 直接用Oracle内置IS_JSON函数筛选非法JSON行：

SELECT ROWID, jsonfile 
FROM json_access_addresses 
WHERE IS_JSON(jsonfile) = 0;

该查询返回的所有行都是存在JSON语法错误的CLOB记录，你可以导出这些记录核对格式，比如你给出的示例JSON里两个数组对象之间缺少逗号，就属于这类典型语法错误。

• 若需要定位数组内单条非法节点，可通过分批遍历缩小范围，每次处理100~1000行原始记录，哪一批报错就继续拆分排查，直到找到具体问题行。

二、实现全量加载 #

修改原有逻辑，改成逐行解析原始CLOB，单独捕获每一行的解析错误，错误行存入专门的错误表后跳过，不影响整体加载流程：

1. 先创建错误记录表，用于存储解析失败的行信息：

CREATE TABLE json_parse_errors (
    err_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    source_rowid ROWID,
    error_msg VARCHAR2(4000)
);

2. 修改后的PL/SQL加载代码如下：

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    -- 遍历原始JSON表的所有行，不提前做JSON解析
    CURSOR c_raw IS SELECT ROWID rid, jsonfile FROM json_access_addresses;
    v_success_cnt NUMBER := 0;
    v_err_msg VARCHAR2(4000);
BEGIN
    -- 清空目标表和错误表，TRUNCATE比DELETE效率更高
    EXECUTE IMMEDIATE 'TRUNCATE TABLE dawa_access_addresses';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE json_parse_errors';

    FOR rec_raw IN c_raw LOOP
        BEGIN
            -- 单独解析当前行的JSON内容，插入目标表
            INSERT INTO dawa_access_addresses(STATUS, SOURCE, CREATED, CHANGED, INFORCE, MUNICIPALITY_CODE, STREET_CODE, STREET_NO, ZIP_CODE, X_COORDINATE, Y_COORDINATE, PROPERTY_ID, ACCURACY, ADDRESS_CHANGE_DATE, ELEVATION, ADDITIONAL_CITY_NAME_ID, ID, ADDITIONAL_CITY_NAME, CADASTRE_ID, STREET_NO_SOURCE, TECHNICAL_STANDARD, TEXT_DIRECTION, ESDHREFERENCE, JOURNAL_NUMBER, ACCESS_POINT_ID, STREET_POINT_ID, NAMED_STREET_ID)
            SELECT STATUS, SOURCE, CREATED, CHANGED, INFORCE,
                   MUNICIPALITY_CODE, STREET_CODE, STREET_NO, ZIP_CODE, X_COORDINATE, Y_COORDINATE, 
                   PROPERTY_ID, ACCURACY, ADDRESS_CHANGE_DATE, ELEVATION, ADDITIONAL_CITY_NAME_ID,
                   ID_1, ADDITIONAL_CITY_NAME, CADASTRE_ID, STREET_NO_SOURCE, TECHNICAL_STANDARD, TEXT_DIRECTION,
                   ESDHREFERENCE, JOURNAL_NUMBER, NAMED_STREET_ID,
                   ACCESS_POINT_ID, STREET_POINT_ID
            FROM JSON_TABLE(rec_raw.jsonfile, '$.adgangsadresser[*]' 
                COLUMNS (
                STATUS      NUMBER PATH '$.status',  
                SOURCE VARCHAR2(64) PATH '$.kilde',
                CREATED VARCHAR2(32) PATH '$.oprettet',
                CHANGED VARCHAR2(32) PATH '$.aendret',
                INFORCE VARCHAR2(32) PATH '$.ikrafttraedelsesdato',
                MUNICIPALITY_CODE VARCHAR2(4) PATH '$.kommunekode',
                STREET_CODE VARCHAR2(4) PATH '$.vejkode',
                STREET_NO VARCHAR2(16) PATH '$.husnr',
                ZIP_CODE VARCHAR2(4) PATH '$.postnr',
                X_COORDINATE VARCHAR2(16) PATH '$.etrs89koordinat_oest',
                Y_COORDINATE VARCHAR2(16) PATH '$.etrs89koordinat_nord',
                PROPERTY_ID VARCHAR2(16) PATH '$.esrejendomsnr',
                ACCURACY VARCHAR2(4) PATH '$.noejagtighed',
                ADDRESS_CHANGE_DATE VARCHAR2(32) PATH '$.adressepunktaendringsdato',
                ELEVATION      VARCHAR2(16) PATH '$.hoejde',  
                ADDITIONAL_CITY_NAME_ID VARCHAR2(64) PATH '$.supplerendebynavn_dagi_id',
                ID_1 VARCHAR2(64) PATH '$.id',
                ADDITIONAL_CITY_NAME VARCHAR2(256) PATH '$.supplerendebynavn',
                CADASTRE_ID VARCHAR2(64) PATH '$.matrikelnr',
                STREET_NO_SOURCE VARCHAR2(16) PATH '$.husnummerkilde',
                TECHNICAL_STANDARD VARCHAR2(16) PATH '$.tekniskstandard',
                TEXT_DIRECTION VARCHAR2(16) PATH '$.tekstretning',
                ESDHREFERENCE VARCHAR2(64) PATH '$.esdhreference',
                JOURNAL_NUMBER VARCHAR2(64) PATH '$.journalnummer',
                ACCESS_POINT_ID VARCHAR2(64) PATH '$.adgangspunktid',
                STREET_POINT_ID VARCHAR2(64) PATH '$.vejpunkt_id',
                NAMED_STREET_ID VARCHAR2(64) PATH '$.navngivenvej_id'
                )
            );
            v_success_cnt := v_success_cnt + SQL%ROWCOUNT;
        EXCEPTION
            WHEN OTHERS THEN
                v_err_msg := SQLERRM;
                -- 错误行存入错误表，不中断流程
                INSERT INTO json_parse_errors(source_rowid, error_msg) VALUES(rec_raw.rid, v_err_msg);
                DBMS_OUTPUT.PUT_LINE('解析失败行ROWID：'||rec_raw.rid||'，错误信息：'||v_err_msg);
        END;
        COMMIT;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('成功加载总条数：'||v_success_cnt);
    DBMS_OUTPUT.PUT_LINE('解析失败总行数：'||(SELECT COUNT(*) FROM json_parse_errors));
END;
/

补充说明 #

如果是Oracle 12.1版本JSON解析容错性低导致的问题，可升级到12.2及以上版本，更高版本支持ON ERROR NULL这类容错参数，可跳过单个错误节点，不会导致整行解析失败。

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