PL/SQL存储过程EMAIL_DUMP报ORA-06502缓冲区过小错误如何解决

• 第一阶段（第73行）报错原因：初始声明l_clob2、l_attach_text2等变量为VARCHAR2(32767)类型，PL/SQL中VARCHAR2的最大长度仅32767字节，当查询返回的CSV数据量超过32K时，字符串拼接就会触发缓冲区不足错误。
• 第二阶段（utl_smtp.WRITE_DATA行）报错原因：UTL_SMTP.WRITE_DATA内置过程的第二个入参仅支持VARCHAR2类型，即使将l_clob2改为CLOB类型，调用时Oracle会尝试将CLOB隐式转换为VARCHAR2，只要CLOB内容超过32K就会再次触发长度超限错误。

按以下步骤修改存储过程即可解决问题：

1. 修改变量声明并初始化CLOB
   将存储过程开头的变量声明替换为以下内容，同时新增CLOB初始化逻辑：

l_clob2  CLOB; -- 改为CLOB类型存储大段CSV内容
l_attach_text2 VARCHAR2 (32767); -- 单行数据不会超过32K，保留VARCHAR2即可
l_attach_text_h2 VARCHAR2 (32767);

-- 原有其他变量声明不变
v_From VARCHAR2(280) := 'ecd.com';
-- ... 剩余原有变量声明保持不变

在BEGIN块的最开头添加CLOB初始化语句：

BEGIN
DBMS_LOB.CREATETEMPORARY(l_clob2, TRUE); -- 初始化临时CLOB
-- 原有后续逻辑保留
l_attach_text_h2 :=
'ID ,INPUT_DATE ,PAYER ,AMOUNT ,TYPE ,PAYEE-SORTCODE_&_BANK_ACCOUNT_NO ,ADDITIONAL_REMARKS ,COMMENTS ,ACCOUNT_NUMBER ,POLICY_NUMBER ,DATE_OF_BRANCH_CONFIRMATION ,CONFIRMED_BY ,SHEETUPDATE_DATE ,MAILUPDATE_DATE ,DATE-TIME ,USER_ID ,STATUS ';

2. 修改CLOB拼接逻辑
   使用DBMS_LOB.APPEND拼接内容，避免普通||拼接的隐式转换问题：

• 表头拼接：在循环开始前写入表头到CLOB

DBMS_LOB.APPEND(l_clob2, l_attach_text_h2 || chr(13) || chr(10));

• 循环内的行拼接：将原有的l_clob2 := l_clob2||chr(10)||l_attach_text2;替换为：

DBMS_LOB.APPEND(l_clob2, l_attach_text2 || chr(10));

• 删除原来的l_clob2 := l_attach_text_h2 ||chr(13)|| l_clob2;语句，因为表头已经提前写入CLOB。

3. 重构UTL_SMTP写入逻辑，分段写入CLOB
   将原来一整段的utl_smtp.WRITE_DATA调用拆分为三部分：先写邮件固定头、再分段写CLOB附件、最后写邮件结束边界：

utl_smtp.OPEN_DATA(v_Mail_Conn);

-- 第一部分：写入邮件头、正文、附件元数据
utl_smtp.WRITE_DATA(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf ||
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'Please find the following in the attachments :'|| crlf ||
'CMTL Entry details & Cash Entry details'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="myFile.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="myFile.csv"'|| crlf ||
crlf
);

-- 第二部分：分段写入CLOB格式的CSV内容，每次写32K以内的片段
DECLARE
  v_offset NUMBER := 1;
  v_buffer VARCHAR2(32767);
  v_clob_len NUMBER := DBMS_LOB.GETLENGTH(l_clob2);
BEGIN
  WHILE v_offset <= v_clob_len LOOP
    v_buffer := DBMS_LOB.SUBSTR(l_clob2, 32767, v_offset);
    utl_smtp.WRITE_DATA(v_Mail_Conn, v_buffer);
    v_offset := v_offset + 32767;
  END LOOP;
END;

-- 第三部分：写入邮件结束边界
utl_smtp.WRITE_DATA(v_Mail_Conn, crlf || crlf || '-------SECBOUND--');

utl_smtp.CLOSE_DATA(v_mail_conn);
utl_smtp.Quit(v_mail_conn);

4. 添加CLOB资源释放逻辑
   在程序正常结束和异常分支都添加临时CLOB释放语句，避免内存泄漏：

-- 正常执行结束前释放
IF DBMS_LOB.ISOPEN(l_clob2) = 1 THEN
  DBMS_LOB.FREETEMPORARY(l_clob2);
END IF;
DBMS_OUTPUT.put_line('mail send  completed...');

EXCEPTION
  WHEN OTHERS THEN
    -- 异常分支也需要释放CLOB
    IF DBMS_LOB.ISOPEN(l_clob2) = 1 THEN
      DBMS_LOB.FREETEMPORARY(l_clob2);
    END IF;
    -- 原有异常处理逻辑保留
    DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);
    system.intranet_utils.INTRANET_LOG_ERRORS('procedure EmailDump',
    system.intranet_utils.INTRANET_GET_ERRMSG, 'Error in EmailDump');

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