Oracle中使用UTL_SMTP发送多收件人（含BCC）报错排查

单个收件人（To/CC/BCC）场景下邮件发送正常，但通过循环调用UTL_SMTP.rcpt处理逗号分隔的多收件人列表时，触发报错：ORA-29279: SMTP permanent error: 550 Internal error: Invalid argument。问题出在多收件人拆分处理的process_recipients存储过程中，以下是相关代码：

处理收件人的process_recipients存储过程 #

PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
    p_list      IN     VARCHAR2) IS
    
    a    DBMS_UTILITY.UNCL_ARRAY;
    len  PLS_INTEGER;
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
    FOR i IN 1..a.COUNT LOOP
        --dbms_output.put_line( a(i) );
        UTL_SMTP.rcpt(p_mail_conn, a(i) );
    END LOOP;

END process_recipients;

send_email存储过程相关代码片段 #

PROCEDURE send_email (
p_mail_server   IN VARCHAR2
, p_mail_port       IN VARCHAR2
, p_from            IN VARCHAR2
, p_to              IN VARCHAR2
, p_cc              IN VARCHAR2 DEFAULT NULL
, p_bcc             IN VARCHAR2 DEFAULT NULL
, p_subject         IN VARCHAR2
, p_html_msg            IN VARCHAR2 DEFAULT NULL    
, p_text_msg            IN VARCHAR2 DEFAULT NULL
) IS

l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';    


 BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_mail_server,p_mail_port);
UTL_SMTP.helo(l_mail_conn, p_mail_server);
UTL_SMTP.mail(l_mail_conn, p_from);
--UTL_SMTP.rcpt(l_mail_conn, p_to);
process_recipients(l_mail_conn, p_to);   
IF p_cc IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_cc);
END IF;
IF p_bcc IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_bcc);
END IF;

执行存储过程的测试代码 #

DECLARE
-- l_tab dbms_utility.lname_array;
html_message VARCHAR2(2000);

BEGIN
 --GZ_SEND_EMAIL.p_send_v_test_email_address('smtp.isu.edu','25','mackaaro@isu.edu','<em>This</em> is a 3nd test message');
 html_message := '<strong>Curabitur a arcu id erat fermentum dapibus et at leo.</strong> <p>Nunc placerat finibus pellentesque. Curabitur id ligula ac dui dictum bibendum. Suspendisse eget neque risus. </p>';
 
 GZ_SEND_EMAIL.send_email(
    p_mail_server => 'smtp.isu.edu'
    , p_mail_port => '25'
    , p_from => 'scholar@isu.edu'
    , p_to => 'mackaaro@isu.edu,aaronmackley01@gmail.com'
    , p_cc => 'snakeriver1701@gmail.com'
    , p_bcc => NULL
    , p_subject => 'Come join me for lunch at 11:07.'
    , p_html_msg => html_message
    , p_text_msg => 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut vel justo quis felis pellentesque iaculis. Sed sed tristique felis, non condimentum erat. Duis maximus, ligula quis imperdiet euismod, velit sem consectetur erat, a pellentesque ex dui id massa. Suspendisse a dolor elit.'
    );
  
END;

多收件人拆分测试代码 #

DECLARE
 a    DBMS_UTILITY.UNCL_ARRAY;
 len  PLS_INTEGER;
 p_list VARCHAR2(200);
BEGIN
    p_list := 'test1@isu.edu,test2@gmail.com,test3@dc1315.com,test4@gmail.com';
    DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
    FOR i IN 1..a.COUNT LOOP
        dbms_output.put_line( a(i) );
    END LOOP;
END; 

问题根源 #

DBMS_UTILITY.COMMA_TO_TABLE设计初衷是处理标识符列表（如表名、列名），它会自动将拆分后的字符串转为大写，若原字符串含小写字母，还会给字符串包裹双引号。例如输入mackaaro@isu.edu，拆分后会变成"MACKAARO@ISU.EDU"，而SMTP服务器无法识别带双引号的收件人地址，从而触发550错误。

解决方案 #

替换DBMS_UTILITY.COMMA_TO_TABLE，改用自定义字符串拆分逻辑，保留邮箱地址原始格式：

优化后的process_recipients存储过程 #

PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
    p_list      IN     VARCHAR2) IS
    
    l_start_pos PLS_INTEGER := 1;
    l_comma_pos PLS_INTEGER;
    l_email     VARCHAR2(255);
BEGIN
    IF p_list IS NULL OR TRIM(p_list) = '' THEN
        RETURN;
    END IF;
    
    LOOP
        l_comma_pos := INSTR(p_list, ',', l_start_pos);
        IF l_comma_pos = 0 THEN
            l_email := TRIM(SUBSTR(p_list, l_start_pos));
            IF l_email IS NOT NULL THEN
                UTL_SMTP.rcpt(p_mail_conn, l_email);
            END IF;
            EXIT;
        ELSE
            l_email := TRIM(SUBSTR(p_list, l_start_pos, l_comma_pos - l_start_pos));
            IF l_email IS NOT NULL THEN
                UTL_SMTP.rcpt(p_mail_conn, l_email);
            END IF;
            l_start_pos := l_comma_pos + 1;
        END IF;
    END LOOP;

END process_recipients;

补充说明 #

1. 自定义拆分逻辑保留邮箱地址原始大小写与格式，不会添加额外符号
2. 加入TRIM处理，避免收件人地址前后空格引发SMTP错误
3. 增加空值判断，防止空字符串或全空格输入引发异常

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