Oracle 12.1中如何在PL/SQL里接收并处理上传的CSV文件

一、直接接收CSV内容为CLOB（绕过BLOB自动存储） #

在mod_plsql环境下，你可以通过owa_upload包直接读取上传的文本文件内容到CLOB，无需依赖PlsqlDocumentTablename指定的表存储BLOB，步骤如下：

1. 确保HTML表单设置正确的编码类型
   用htp.p()生成表单时，必须包含enctype="multipart/form-data"属性，示例代码：

htp.p('<form method="post" action="your_plsql_procedure" enctype="multipart/form-data">');
htp.p('<input type="file" name="csv_file" accept=".csv">');
htp.p('<input type="submit" value="上传">');
htp.p('</form>');

2. 在PL/SQL过程中读取文件内容到CLOB

CREATE OR REPLACE PROCEDURE process_csv_upload
IS
    l_file_index    PLS_INTEGER;
    l_clob_content  CLOB;
    l_buffer        VARCHAR2(32767);
    l_amount        PLS_INTEGER := 32767;
    l_offset        PLS_INTEGER := 1;
BEGIN
    -- 获取上传文件的索引（对应表单中文件控件的name属性）
    l_file_index := owa_upload.get_file_index('csv_file');
    
    -- 初始化临时CLOB
    dbms_lob.createtemporary(l_clob_content, TRUE);
    
    -- 逐块读取文件内容写入CLOB
    LOOP
        owa_upload.read(l_file_index, l_buffer, l_amount, l_offset);
        EXIT WHEN l_buffer IS NULL;
        dbms_lob.writeappend(l_clob_content, length(l_buffer), l_buffer);
        l_offset := l_offset + l_amount;
    END LOOP;
    
    -- 调用自定义逻辑处理CSV内容
    parse_csv_clob(l_clob_content);
    
    -- 释放临时CLOB
    dbms_lob.freetemporary(l_clob_content);
EXCEPTION
    WHEN OTHERS THEN
        htp.p('上传处理失败: ' || SQLERRM);
        IF dbms_lob.istemporary(l_clob_content) = 1 THEN
            dbms_lob.freetemporary(l_clob_content);
        END IF;
END;
/

注意：需确保数据库用户拥有OWA_UPLOAD包的执行权限，可通过GRANT EXECUTE ON owa_upload TO your_user;授权。

二、从BLOB转换为CLOB并逐行解析 #

如果无法绕开BLOB存储机制，可将BLOB转换为CLOB后再逐行处理，核心是利用dbms_lob.converttoclob完成字符集转换，再通过定位换行符拆分每行。

1. BLOB转CLOB函数 #

CREATE OR REPLACE FUNCTION blob_to_clob(p_blob IN BLOB, p_charset IN VARCHAR2 DEFAULT 'AL32UTF8')
RETURN CLOB
IS
    l_clob         CLOB;
    l_dest_offset  PLS_INTEGER := 1;
    l_src_offset   PLS_INTEGER := 1;
    l_lang_context PLS_INTEGER := dbms_lob.default_lang_ctx;
    l_warning      PLS_INTEGER;
BEGIN
    dbms_lob.createtemporary(l_clob, TRUE);
    dbms_lob.converttoclob(
        dest_lob     => l_clob,
        src_blob     => p_blob,
        amount       => dbms_lob.lobmaxsize,
        dest_offset  => l_dest_offset,
        src_offset   => l_src_offset,
        blob_csid    => nls_charset_id(p_charset),
        lang_context => l_lang_context,
        warning      => l_warning
    );
    RETURN l_clob;
EXCEPTION
    WHEN OTHERS THEN
        IF dbms_lob.istemporary(l_clob) = 1 THEN
            dbms_lob.freetemporary(l_clob);
        END IF;
        RAISE;
END;
/

注意：p_charset需匹配CSV文件的实际字符集，比如Windows生成的CSV可能用ZHS16GBK，需根据实际情况调整。

2. 逐行解析CLOB内容 #

CREATE OR REPLACE PROCEDURE parse_csv_clob(p_clob IN CLOB)
IS
    l_line         VARCHAR2(32767);
    l_start_pos    PLS_INTEGER := 1;
    l_end_pos      PLS_INTEGER;
    -- 换行符：Windows格式用CHR(13)||CHR(10)，Unix/Linux用CHR(10)
    l_line_sep     VARCHAR2(2) := CHR(10);
BEGIN
    LOOP
        -- 定位下一个换行符的位置
        l_end_pos := dbms_lob.instr(p_clob, l_line_sep, l_start_pos);
        
        -- 截取当前行
        IF l_end_pos > 0 THEN
            l_line := dbms_lob.substr(p_clob, l_end_pos - l_start_pos, l_start_pos);
            l_start_pos := l_end_pos + length(l_line_sep);
        ELSE
            -- 处理最后一行（无结尾换行符）
            l_line := dbms_lob.substr(p_clob, dbms_lob.getlength(p_clob) - l_start_pos + 1, l_start_pos);
            l_start_pos := dbms_lob.getlength(p_clob) + 1;
        END IF;
        
        -- 跳过空行（可选）
        IF TRIM(l_line) IS NOT NULL THEN
            -- 调用单行处理逻辑
            process_csv_line(l_line);
        END IF;
        
        EXIT WHEN l_start_pos > dbms_lob.getlength(p_clob);
    END LOOP;
END;
/

-- 示例：拆分CSV行字段（处理带引号的逗号分隔字段）
CREATE OR REPLACE PROCEDURE process_csv_line(p_line IN VARCHAR2)
IS
    l_field_count  PLS_INTEGER;
    l_field        VARCHAR2(32767);
BEGIN
    -- 正则表达式拆分CSV字段，支持引号包裹的含逗号内容
    FOR i IN 1..regexp_count(p_line, '(".*?"|[^",]+)(,|$)') LOOP
        l_field := regexp_substr(p_line, '(".*?"|[^",]+)(,|$)', 1, i, null, 1);
        -- 去除字段首尾引号（可选）
        l_field := regexp_replace(l_field, '^"|"$', '');
        -- 这里根据实际表结构插入数据
        -- INSERT INTO your_table(colX) VALUES (l_field);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        htp.p('处理行失败: ' || p_line || '，错误信息: ' || SQLERRM);
END;
/

三、注意事项 #

• 字符集一致性：无论直接接收还是BLOB转CLOB，必须确保字符集匹配，避免乱码。
• 大文件处理：若CSV文件过大（如超过100MB），直接用CLOB可能占用过多内存，可考虑分块处理。
• 权限配置：确保PL/SQL执行用户拥有LOB操作、目标表写入等权限。

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