Oracle 19C大体积CSV（BLOB存储）PL/SQL导入过程优化问询

核心思路 #

放弃一次性将ZIP内的CSV BLOB转为完整CLOB的方案，改为分段读取+逐段处理的流式逻辑，从根源上减少内存占用，规避大对象处理引发的报错。

具体优化步骤 #

1. 流式读取ZIP内的CSV内容 #

使用UTL_ZIP的分段读取API，直接从ZIP BLOB中读取CSV文件的部分内容，无需先将整个CSV文件提取为完整BLOB。

DECLARE
    v_zip_blob BLOB; -- 从表中获取的ZIP压缩包BLOB
    v_zip_handle UTL_ZIP.ZIP_FILE_HANDLE;
    v_file_entry UTL_ZIP.ZIP_ENTRY;
    v_buffer RAW(32767);
    v_bytes_read PLS_INTEGER;
    v_line_buffer VARCHAR2(32767); -- 缓存跨行的内容片段
BEGIN
    -- 打开ZIP BLOB
    v_zip_handle := UTL_ZIP.open_zip(v_zip_blob);
    v_file_entry := UTL_ZIP.get_next_entry(v_zip_handle);

    -- 遍历ZIP内的文件，定位目标CSV
    WHILE v_file_entry IS NOT NULL LOOP
        IF LOWER(v_file_entry.name) LIKE '%.csv' THEN
            -- 循环分段读取CSV内容
            LOOP
                UTL_ZIP.read_entry(v_zip_handle, v_file_entry, v_buffer, 32767, v_bytes_read);
                EXIT WHEN v_bytes_read = 0;

                -- 将RAW片段转为字符串，合并到行缓冲区
                v_line_buffer := v_line_buffer || UTL_RAW.CAST_TO_VARCHAR2(v_buffer);
                -- 处理缓冲区中的完整行
                process_csv_lines(v_line_buffer);
            END LOOP;

            -- 处理最后剩余的缓冲区内容
            IF TRIM(v_line_buffer) IS NOT NULL THEN
                process_csv_lines(v_line_buffer);
            END IF;
        END IF;
        v_file_entry := UTL_ZIP.get_next_entry(v_zip_handle);
    END LOOP;

    UTL_ZIP.close_zip(v_zip_handle);
END;

2. 实现逐行拆分与批量插入的自定义过程 #

自定义process_csv_lines过程，负责拆分缓冲区中的完整行，用正则表达式解析列，并通过FORALL批量插入目标表，提升处理效率。

PROCEDURE process_csv_lines(p_buffer IN OUT VARCHAR2) IS
    v_line VARCHAR2(32767);
    v_line_start PLS_INTEGER := 1;
    v_line_end PLS_INTEGER;
    v_delim CHAR(1) := CHR(10);
    -- 定义目标表对应的变量/集合
    TYPE t_target_row IS RECORD (
        col1 VARCHAR2(100),
        col2 NUMBER,
        col3 DATE
    );
    TYPE t_target_batch IS TABLE OF t_target_row;
    v_batch t_target_batch := t_target_batch();
    v_batch_size CONSTANT PLS_INTEGER := 1000; -- 批量大小可根据性能调整
BEGIN
    LOOP
        v_line_end := INSTR(p_buffer, v_delim, v_line_start);
        EXIT WHEN v_line_end = 0;

        -- 提取完整行
        v_line := SUBSTR(p_buffer, v_line_start, v_line_end - v_line_start);
        v_line_start := v_line_end + 1;

        -- 跳过空行和表头（按需调整判断逻辑）
        IF TRIM(v_line) = '' OR v_line LIKE 'col1,col2,col3%' THEN
            CONTINUE;
        END IF;

        -- 用正则表达式拆分列（根据CSV实际格式调整正则）
        v_batch.EXTEND;
        v_batch(v_batch.LAST).col1 := REGEXP_SUBSTR(v_line, '([^,]+)', 1, 1);
        v_batch(v_batch.LAST).col2 := TO_NUMBER(REGEXP_SUBSTR(v_line, '([^,]+)', 1, 2));
        v_batch(v_batch.LAST).col3 := TO_DATE(REGEXP_SUBSTR(v_line, '([^,]+)', 1, 3), 'YYYY-MM-DD');

        -- 达到批量大小则执行插入
        IF v_batch.COUNT >= v_batch_size THEN
            FORALL i IN v_batch.FIRST..v_batch.LAST
                INSERT INTO target_table VALUES v_batch(i);
            COMMIT; -- 按需选择提交时机，或使用自治事务
            v_batch.DELETE;
        END IF;
    END LOOP;

    -- 将未处理的跨行内容放回缓冲区
    p_buffer := SUBSTR(p_buffer, v_line_start);

    -- 处理最后一批剩余数据
    IF v_batch.COUNT > 0 THEN
        FORALL i IN v_batch.FIRST..v_batch.LAST
            INSERT INTO target_table VALUES v_batch(i);
        COMMIT;
        v_batch.DELETE;
    END IF;
END;

关键优化说明 #

• 分段流式处理：避免一次性加载大对象到内存，降低PGA消耗，彻底解决大文件转CLOB后的内存溢出问题。
• 批量插入：使用FORALL替代单条插入，大幅减少SQL执行次数，提升插入性能并降低日志生成量。
• 跨行缓存：维护行缓冲区解决分段读取时内容被截断在行中间的问题，确保数据完整性。
• 字符集适配：确保UTL_RAW.CAST_TO_VARCHAR2使用的字符集与CSV文件一致，避免出现乱码。

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