如何用PL/SQL的JSON_TABLE、BFILENAME和BULK COLLECT加载JSON到嵌套表

前提准备 #

• 创建数据库目录对象，映射JSON文件所在的操作系统路径：

CREATE OR REPLACE DIRECTORY JSON_DIR AS '/your/actual/json/file/path';

• 为当前用户授予该目录的读取权限：

GRANT READ ON DIRECTORY JSON_DIR TO your_user;

完整PL/SQL实现代码 #

DECLARE
    TYPE list_t IS TABLE OF VARCHAR2(100);
    
    l_included_errors list_t;
    l_excluded_errors list_t;
    
    l_json_clob CLOB;
    l_bfile BFILE;
    l_dest_offset NUMBER := 1;
    l_src_offset NUMBER := 1;
    l_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    l_warning NUMBER;
BEGIN
    -- 读取JSON文件到临时CLOB
    l_bfile := BFILENAME('JSON_DIR', 'your_json_file.json');
    DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.CREATETEMPORARY(l_json_clob, TRUE);
    DBMS_LOB.LOADCLOBFROMFILE(
        dest_lob => l_json_clob,
        src_bfile => l_bfile,
        amount => DBMS_LOB.LOBMAXSIZE,
        dest_offset => l_dest_offset,
        src_offset => l_src_offset,
        bfile_csid => DBMS_LOB.DEFAULT_CSID,
        lang_context => l_lang_context,
        warning => l_warning
    );
    DBMS_LOB.CLOSE(l_bfile);
    
    -- 解析JSON并批量加载到嵌套表
    SELECT included, excluded
    BULK COLLECT INTO l_included_errors, l_excluded_errors
    FROM JSON_TABLE(
        l_json_clob,
        '$' COLUMNS (
            NESTED PATH '$.included_errors[*]' COLUMN (included VARCHAR2(100) PATH '$'),
            NESTED PATH '$.excluded_errors[*]' COLUMN (excluded VARCHAR2(100) PATH '$')
        )
    );
    
    -- 遍历验证示例
    DBMS_OUTPUT.PUT_LINE('=== Included Errors ===');
    FOR i IN 1..l_included_errors.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_included_errors(i));
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('=== Excluded Errors ===');
    FOR i IN 1..l_excluded_errors.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_excluded_errors(i));
    END LOOP;
    
    -- 清理临时CLOB
    DBMS_LOB.FREETEMPORARY(l_json_clob);
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_LOB.ISOPEN(l_bfile) = 1 THEN
            DBMS_LOB.CLOSE(l_bfile);
        END IF;
        IF DBMS_LOB.ISTEMPORARY(l_json_clob) = 1 THEN
            DBMS_LOB.FREETEMPORARY(l_json_clob);
        END IF;
        RAISE;
END;
/

关键逻辑说明 #

• BFILENAME + DBMS_LOB：通过BFILENAME获取操作系统文件的BFILE指针，再用LOADCLOBFROMFILE将文件内容加载到临时CLOB，为JSON解析提供数据源。
• JSON_TABLE：利用NESTED PATH遍历JSON数组的每个元素，将非结构化的JSON数组转换为关系型数据列。
• BULK COLLECT：一次性将查询结果批量写入嵌套表变量，无需创建中间数据库表，直接实现内存级数据加载。

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