Oracle并行导出函数执行报错ORA-06502：字符缓冲区过小

问题背景 #

环境：Linux 8.10、Oracle 19.18
通过自定义PL/SQL函数fn_generate_parallel_file实现大表并行导出，将数据拆分为多个文本文件后在操作系统合并。导出列数较少的表（如ODSACCOUNT）时正常，但导出多列的ODSAGREEMENT表时触发错误：

ERROR at line 1:
ORA-12801: error signaled in parallel query server P006
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FN_GENERATE_PARALLEL_FILE", line 34
ORA-06512: at line 1

原函数代码：

create or replace type dump_parallel_object AS OBJECT( file_name  VARCHAR2(256), no_records NUMBER, seq_id     NUMBER);/
create or replace type dump_parallel_object_ntt AS TABLE OF dump_parallel_object ;/
create or replace function fn_generate_parallel_file (p_source    IN SYS_REFCURSOR,p_filename  IN VARCHAR2,p_directory IN VARCHAR2,p_limit     IN NUMBER DEFAULT 1000) return dump_parallel_object_ntt pipelined parallel_enable (partition p_source by any) as type row_ntt is table of varchar2(32767); v_rows    row_ntt; v_file    UTL_FILE.FILE_TYPE; v_buffer  VARCHAR2(32767); v_sid     NUMBER; v_name    VARCHAR2(512); v_lines   PLS_INTEGER := 0; c_eol     CONSTANT VARCHAR2(1) := CHR(10); c_eollen  CONSTANT PLS_INTEGER := LENGTH(c_eol); c_maxline CONSTANT PLS_INTEGER := 32767; begin SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1; v_name := p_filename || '_' || TO_CHAR(v_sid) || '.txt'; v_file := UTL_FILE.FOPEN(p_directory, v_name, 'w', 32767); LOOP FETCH p_source BULK COLLECT INTO v_rows LIMIT p_limit; FOR i IN 1 .. v_rows.COUNT LOOP IF LENGTH(v_buffer) + c_eollen + LENGTH(v_rows(i)) <= c_maxline THEN v_buffer := v_buffer || c_eol || v_rows(i); ELSE IF v_buffer IS NOT NULL THEN UTL_FILE.PUT_LINE(v_file, v_buffer); END IF; v_buffer := v_rows(i); END IF; END LOOP; v_lines := v_lines + v_rows.COUNT; EXIT WHEN p_source%NOTFOUND; END LOOP; CLOSE p_source; UTL_FILE.PUT_LINE(v_file, v_buffer); UTL_FILE.FCLOSE(v_file); PIPE ROW (dump_parallel_object(v_name, v_lines, v_sid)); RETURN; END fn_generate_parallel_file;/

错误原因分析 #

错误根源是单条拼接后的CSV记录长度超过了PL/SQL中VARCHAR2的最大限制32767字节：

• 原函数中row_ntt定义为TABLE OF VARCHAR2(32767)，当多列拼接后的单条记录长度超过32767时，FETCH p_source BULK COLLECT INTO v_rows会直接触发缓冲区溢出
• 即使记录长度刚好接近32767，后续拼接换行符到v_buffer时也会超过c_maxline限制，触发ORA-06502

解决方案：改用CLOB处理大长度记录 #

将函数中的字符串类型替换为CLOB（支持最大4GB存储），同时调整文件写入逻辑以适配CLOB：

修改后的函数代码 #

create or replace type dump_parallel_object AS OBJECT( file_name  VARCHAR2(256), no_records NUMBER, seq_id     NUMBER);/
create or replace type dump_parallel_object_ntt AS TABLE OF dump_parallel_object ;/
create or replace function fn_generate_parallel_file (
    p_source    IN SYS_REFCURSOR,
    p_filename  IN VARCHAR2,
    p_directory IN VARCHAR2,
    p_limit     IN NUMBER DEFAULT 1000
) return dump_parallel_object_ntt pipelined 
parallel_enable (partition p_source by any) as 
    type row_ntt is table of CLOB; 
    v_rows    row_ntt; 
    v_file    UTL_FILE.FILE_TYPE; 
    v_buffer  CLOB; 
    v_sid     NUMBER; 
    v_name    VARCHAR2(512); 
    v_lines   PLS_INTEGER := 0; 
    c_eol     CONSTANT VARCHAR2(1) := CHR(10); 
    c_chunk_size CONSTANT PLS_INTEGER := 32767; -- 分段写入的块大小
begin 
    SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1; 
    v_name := p_filename || '_' || TO_CHAR(v_sid) || '.txt'; 
    v_file := UTL_FILE.FOPEN(p_directory, v_name, 'w', c_chunk_size); 
    
    LOOP 
        FETCH p_source BULK COLLECT INTO v_rows LIMIT p_limit; 
        
        FOR i IN 1 .. v_rows.COUNT LOOP 
            -- 拼接当前记录到缓冲区，添加换行符
            IF v_buffer IS NULL THEN
                v_buffer := v_rows(i);
            ELSE
                v_buffer := v_buffer || c_eol || v_rows(i);
            END IF;
            
            -- 当缓冲区大小超过块大小，分段写入文件
            WHILE DBMS_LOB.GETLENGTH(v_buffer) > c_chunk_size LOOP
                UTL_FILE.PUT(v_file, DBMS_LOB.SUBSTR(v_buffer, c_chunk_size));
                DBMS_LOB.TRIM(v_buffer, DBMS_LOB.GETLENGTH(v_buffer) - c_chunk_size);
            END LOOP;
        END LOOP;
        
        v_lines := v_lines + v_rows.COUNT; 
        EXIT WHEN p_source%NOTFOUND; 
    END LOOP; 
    
    CLOSE p_source; 
    
    -- 写入剩余的缓冲区内容
    IF DBMS_LOB.GETLENGTH(v_buffer) > 0 THEN
        UTL_FILE.PUT(v_file, v_buffer);
    END IF;
    
    UTL_FILE.NEW_LINE(v_file); -- 添加最后一行换行
    UTL_FILE.FCLOSE(v_file); 
    
    PIPE ROW (dump_parallel_object(v_name, v_lines, v_sid)); 
    RETURN; 
END fn_generate_parallel_file;/

关键修改点 #

1. 将row_ntt类型从VARCHAR2(32767)改为CLOB，支持超长记录存储
2. v_buffer替换为CLOB类型，避免拼接时的长度限制
3. 新增分段写入逻辑：当CLOB缓冲区超过32767字节时，分块写入文件，避免UTL_FILE的单次写入长度限制
4. 使用DBMS_LOB包的函数处理CLOB的长度、截取和修剪操作

调用注意事项 #

• 确保数据库目录DIR_ODSVIEWS有足够的读写权限，且操作系统层面目录存在
• 并行度设置需匹配服务器CPU核心数，避免资源耗尽
• 若单条记录长度远超32767，该方案仍可正常处理，因为CLOB支持超大容量

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