Oracle 12c数据库：从文件导入JSON至CLOB字段的正确方法咨询

当然有规范的实现方法！针对Oracle 12c将JSON文件导入CLOB字段，我整理了几种常用且可靠的方案，你可以根据自己的场景灵活选择：

这是Oracle官方推荐的批量数据导入工具，处理大文件效率很高。步骤如下：

1. 创建目标表：先确保你有存储CLOB的表，比如：

CREATE TABLE json_clob_table (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    json_data CLOB,
    load_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

2. 编写SQL*Loader控制文件（比如命名为json_loader.ctl）：

LOAD DATA
INFILE 'path/to/your/json/files/*.json' -- 单个文件或通配符匹配多个文件
APPEND INTO TABLE json_clob_table
FIELDS TERMINATED BY WHITESPACE -- 因为JSON是单条数据，用这个避免拆分
(
    dummy FILLER CHAR(1), -- 占位符，因为我们要读取整个文件内容
    json_data LOBFILE(CONSTANT 'path/to/your/json/files/:' || FILENAME) TERMINATED BY EOF -- 读取整个文件到CLOB
)

注意：如果是单个JSON文件，直接写文件路径即可；如果是多个文件，用通配符并结合FILENAME变量来动态读取每个文件。

3. 执行SQL*Loader命令：

sqlldr username/password@database control=json_loader.ctl log=json_loader.log

如果需要在导入时做一些自定义逻辑（比如数据校验、预处理），用PL/SQL结合UTL_FILE包是个不错的选择：

1. 创建数据库目录对象（指向JSON文件所在的操作系统路径）：

CREATE OR REPLACE DIRECTORY json_dir AS '/path/to/your/json/files';
GRANT READ, WRITE ON DIRECTORY json_dir TO your_username;

2. 编写PL/SQL匿名块或存储过程：

DECLARE
    v_file UTL_FILE.FILE_TYPE;
    v_clob CLOB;
    v_buffer VARCHAR2(32767);
BEGIN
    -- 初始化CLOB
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    
    -- 打开JSON文件
    v_file := UTL_FILE.FOPEN('JSON_DIR', 'your_file.json', 'R', 32767);
    
    -- 逐行读取文件并写入CLOB
    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_file, v_buffer);
            DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_buffer), v_buffer);
            -- 加上换行符，避免JSON内容丢失格式
            DBMS_LOB.WRITEAPPEND(v_clob, 1, CHR(10));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT;
        END;
    END LOOP;
    
    -- 将CLOB插入目标表
    INSERT INTO json_clob_table (json_data) VALUES (v_clob);
    COMMIT;
    
    -- 关闭文件和释放临时CLOB
    UTL_FILE.FCLOSE(v_file);
    DBMS_LOB.FREETEMPORARY(v_clob);
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        DBMS_LOB.FREETEMPORARY(v_clob);
        RAISE;
END;
/

如果需要定期导入新的JSON文件，外部表可以让你像查询普通表一样读取文件内容，再插入到CLOB表：

1. 创建外部表：

CREATE TABLE json_external_table (
    json_data CLOB
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY json_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY 0X'0A' -- 按行分隔，根据实际情况调整
        FIELDS (
            json_data CHAR(4000) -- 如果JSON超过4000字符，改用CLOB定义
        )
    )
    LOCATION ('your_file.json') -- 可以是多个文件，用逗号分隔
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

2. 将外部表数据插入目标CLOB表：

INSERT INTO json_clob_table (json_data)
SELECT json_data FROM json_external_table;
COMMIT;

• 编码一致性：确保JSON文件的编码（比如UTF-8）和数据库的字符集匹配，避免乱码。如果是UTF-8文件，在SQL*Loader控制文件或外部表中可以指定CHARACTERSET AL32UTF8。
• 大文件处理：如果JSON文件特别大（超过GB级），建议用SQL*Loader或外部表，它们对大文件的支持更稳定。
• JSON合法性：如果需要确保导入的JSON是合法的，Oracle 12c可以用JSON_VALID()函数校验，比如在插入时加WHERE JSON_VALID(json_data) = 1。

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