使用PL/SQL的UTL_SMTP发送多行内容附件失败求助

问题根源 #

1. 错误的utl_smtp.data调用时机：原代码在循环内重复调用utl_smtp.data，每次调用都会重置邮件的数据段，导致只有最后一次循环的内容被保留，前面的行全部丢失。
2. 附件内容未累积：每次循环重置v_clob_lines，没有将所有行的数据拼接成完整的CSV内容，而是单独处理单行，最终附件仅包含最后一行数据。
3. MIME结构混乱：邮件的MIME边界、头信息和内容被拆分到循环中发送，违反了SMTP协议的邮件结构规范。

修正后的代码 #

CREATE OR REPLACE PACKAGE BODY xx_common_alerts_pkg AS

    gv_process_name   CONSTANT VARCHAR2(100) := 'Common Alert Functionality';

    PROCEDURE main (
        po_errbuf      OUT            VARCHAR2,
        po_retcode     OUT            NUMBER,
        p_alert_name   IN             VARCHAR2
    ) IS

        lv_procedure             CONSTANT VARCHAR2(200) := 'XX_COMMON_ALERTS_PKG.main';
        lv_to_recipients         alr_actions.to_recipients%TYPE;
        lv_cc_recipients         alr_actions.cc_recipients%TYPE;
        lv_bcc_recipients        alr_actions.bcc_recipients%TYPE;
        lv_subject               alr_actions.subject%TYPE;
        lv_alert_id              alr_alerts.alert_id%TYPE;
        lv_row_count             alr_action_set_checks.row_count%TYPE;
        lv_check_id              alr_action_set_checks.alert_check_id%TYPE;
        v_clob_header            CLOB := empty_clob(); -- 存储CSV表头
        v_clob_attach_content    CLOB := empty_clob(); -- 存储完整附件内容
        lv_body                  alr_actions.body%TYPE;
        v_from                   VARCHAR2(80) := 'abc@gmail.com';
        v_recipient              VARCHAR2(80) := 'def@gmail.com';
        v_subject                VARCHAR2(80) := 'test';
        v_mail_host              VARCHAR2(80) := 'mlocalhost';
        v_smtp_port              NUMBER := 25;
        v_mail_conn              utl_smtp.connection;
        crlf                     VARCHAR2(2) := chr(13) || chr(10);
        le_mail_excp EXCEPTION;

        CURSOR c_get_alert_outputs (
            pi_alert_id VARCHAR2
        ) IS
        SELECT name, title
        FROM alr_alert_outputs
        WHERE alert_id = pi_alert_id
          AND end_date_active IS NULL
        ORDER BY name DESC;

        CURSOR c_output_lines (
            pi_check_id NUMBER,
            pi_row_number NUMBER
        ) IS
        SELECT DISTINCT value, name
        FROM alr_output_history
        WHERE check_id = pi_check_id
          AND row_number = pi_row_number
        ORDER BY name DESC;

    BEGIN   
        -- 获取邮件配置信息
        SELECT actions.to_recipients,
               actions.cc_recipients,
               actions.bcc_recipients,
               actions.subject,
               alr.alert_id,
               actions.body
        INTO lv_to_recipients,
             lv_cc_recipients,
             lv_bcc_recipients,
             lv_subject,
             lv_alert_id,
             lv_body
        FROM alr_alerts alr
        JOIN alr_actions actions ON alr.alert_id = actions.alert_id
        WHERE alr.alert_name = p_alert_name
          AND actions.name = 'Send Email'
          AND actions.enabled_flag = 'Y'
          AND actions.end_date_active IS NULL;

        -- 获取分发列表（如果有）
        IF lv_to_recipients IS NULL OR lv_to_recipients = '' THEN
            SELECT to_recipients, cc_recipients, bcc_recipients
            INTO lv_to_recipients, lv_cc_recipients, lv_bcc_recipients
            FROM alr_distribution_lists
            WHERE list_id = (SELECT list_id FROM alr_actions WHERE alert_id = lv_alert_id AND name = 'Send Email')
              AND application_id = (SELECT list_application_id FROM alr_actions WHERE alert_id = lv_alert_id AND name = 'Send Email')
              AND enabled_flag = 'Y'
              AND end_date_active IS NULL;
        END IF;

        -- 获取最新的检查记录
        SELECT row_count, check_id
        INTO lv_row_count, lv_check_id
        FROM alr_action_set_checks
        WHERE alert_id = lv_alert_id
          AND alert_check_id = (SELECT MAX(alert_check_id) FROM alr_action_set_checks WHERE alert_id = lv_alert_id);

        -- 构建CSV表头
        FOR rec_alert_outputs IN c_get_alert_outputs(lv_alert_id) LOOP
            v_clob_header := rec_alert_outputs.title || ',' || v_clob_header;
        END LOOP;
        -- 移除末尾多余的逗号
        v_clob_header := RTRIM(v_clob_header, ',');
        -- 将表头加入附件内容
        v_clob_attach_content := v_clob_header || crlf;

        -- 构建CSV所有行数据
        FOR i IN 1..lv_row_count LOOP
            DECLARE
                v_line_content VARCHAR2(32000) := '';
            BEGIN
                FOR rec_lines IN c_output_lines(lv_check_id, i) LOOP
                    v_line_content := rec_lines.value || ',' || v_line_content;
                END LOOP;
                -- 移除末尾多余的逗号，加入换行
                v_line_content := RTRIM(v_line_content, ',') || crlf;
                -- 将当前行追加到附件内容CLOB
                v_clob_attach_content := v_clob_attach_content || v_line_content;
            END;
        END LOOP;

        -- 建立SMTP连接
        v_mail_conn := utl_smtp.open_connection(v_mail_host, v_smtp_port);
        utl_smtp.helo(v_mail_conn, v_mail_host);
        utl_smtp.mail(v_mail_conn, v_from);
        utl_smtp.rcpt(v_mail_conn, v_recipient);

        -- 初始化邮件数据段
        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);
        utl_smtp.write_data(v_mail_conn, 'From: ' || v_from || crlf);
        utl_smtp.write_data(v_mail_conn, 'Subject: ' || v_subject || crlf);
        utl_smtp.write_data(v_mail_conn, 'To: ' || v_recipient || crlf);
        utl_smtp.write_data(v_mail_conn, 'MIME-Version: 1.0' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="-----SECBOUND"' || crlf);
        utl_smtp.write_data(v_mail_conn, crlf);

        -- 发送邮件正文部分
        utl_smtp.write_data(v_mail_conn, '-------SECBOUND' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Type: text/plain; charset=UTF-8' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: 7bit' || crlf);
        utl_smtp.write_data(v_mail_conn, crlf);
        utl_smtp.write_data(v_mail_conn, lv_body || crlf);
        utl_smtp.write_data(v_mail_conn, crlf);

        -- 发送附件部分
        utl_smtp.write_data(v_mail_conn, '-------SECBOUND' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Type: text/csv; name="Mismatch.csv"' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: 8bit' || crlf);
        utl_smtp.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="Mismatch.csv"' || crlf);
        utl_smtp.write_data(v_mail_conn, crlf);
        -- 发送完整的附件内容
        utl_smtp.write_data(v_mail_conn, v_clob_attach_content);
        utl_smtp.write_data(v_mail_conn, crlf);

        -- 结束MIME边界
        utl_smtp.write_data(v_mail_conn, '-------SECBOUND--' || crlf);

        -- 关闭数据段并退出连接
        utl_smtp.close_data(v_mail_conn);
        utl_smtp.quit(v_mail_conn);

        po_retcode := 0;
        po_errbuf := '邮件发送成功';

    EXCEPTION
        WHEN OTHERS THEN
            po_errbuf := p_alert_name || ': ' || sqlerrm;
            po_retcode := 1;
            -- 异常时关闭连接（如果已打开）
            IF utl_smtp.is_connected(v_mail_conn) THEN
                utl_smtp.quit(v_mail_conn);
            END IF;
    END main;

END xx_common_alerts_pkg;

关键修改说明 #

1. 分离邮件结构与内容构建：先在循环外构建完整的附件内容（表头+所有行），再一次性发送邮件的全部结构，避免重复初始化数据段。
2. 正确使用SMTP API：使用utl_smtp.open_data()初始化数据段，然后用utl_smtp.write_data()逐段发送邮件头、正文、附件，最后用close_data()结束数据发送。
3. CSV内容格式化：移除每行末尾多余的逗号，确保CSV格式正确；将所有行数据累积到一个CLOB中，保证附件包含完整的多行内容。
4. 完善异常处理：在异常时检查SMTP连接状态并关闭，避免资源泄漏；同时设置正确的返回码和错误信息。

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