如何基于UTL_SMTP重构Oracle APEX带附件邮件发送代码

前置检查 #

• 先为数据库APEX运行用户授予UTL_SMTP、UTL_TCP、UTL_ENCODE、DBMS_LOB包的执行权限
• 配置对应SMTP服务器的ACL访问规则，允许APEX运行用户访问SMTP主机的对应端口
• 修正原代码里的SMTP端口笔误：原代码写的45不符合标准SMTP端口规则，请替换为实际环境端口（常用端口为25/465/587）
• 原有APEX邮件模板无需修改，直接通过内置API渲染占位符内容即可

改造后完整可运行代码 #

declare 
    l_context apex_exec.t_context;    
    l_emailsidx  pls_integer;
    l_namesids    pls_integer;
    l_region_id number;

    -- SMTP配置参数，根据实际环境修改
    l_smtp_host varchar2(100) := 'xyz.xmp.it';
    l_smtp_port number := 25;
    l_smtp_user varchar2(100) := ''; -- 需SMTP认证时填写用户名，无认证留空
    l_smtp_pass varchar2(100) := ''; -- 需SMTP认证时填写密码，无认证留空
    l_sender varchar2(100) := 'example@ac.cc'; -- 替换为实际发件人邮箱
    l_mail_subject varchar2(200) := '迪拜分行2020新年促销通知'; -- 替换为实际邮件主题

    -- 邮件发送相关变量
    l_conn utl_smtp.connection;
    l_boundary varchar2(50) := '----APEXMAILBDR'||to_char(sysdate,'YYYYMMDDHH24MISS');
    l_mail_html clob;
    l_attachment blob;
    l_attach_filename varchar2(200);
    l_attach_mime varchar2(100);
    l_raw_attach raw(32767);
    l_attach_offset number := 1;
    l_chunk_size number := 32767;
begin
    -- 获取CUSTOMERS交互式报表区域ID
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = 1
       and static_id      = 'CUSTOMERS';
 
    l_context := apex_region.open_query_context (
                        p_page_id => 1,
                        p_region_id => l_region_id );
    -- 获取EMAIL和NAME列的位置
    l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
    l_namesids := apex_exec.get_column_position( l_context, 'NAME' );

    -- 提前读取附件内容，避免循环重复查询
    select blob_content, filename, mime_type 
      into l_attachment, l_attach_filename, l_attach_mime
      from apex_application_files
     where flow_id = :APP_ID
       and filename = 'apex_logo.png';
    
    -- 遍历收件人列表循环发信
    while apex_exec.next_row( l_context ) loop  
        -- 渲染APEX邮件模板，保留原有占位符替换逻辑
        l_mail_html := apex_mail.prepare_template (
            p_template_static_id => 'NEW_YEAR_2020_PROMOTION_DUBAI_BRANCH',
            p_placeholders       => '{' ||
            '    "CUSTOMER":'            || apex_json.stringify( apex_exec.get_varchar2( l_context, l_namesids )) ||
            '   ,"START_DATE":'          || apex_json.stringify( :P2_START_DATE ) ||
            '   ,"END_DATE":'            || apex_json.stringify( :P2_END_DATE ) ||        
            '   ,"LOCATION":'            || apex_json.stringify( :P2_LOCATION ) ||        
            '   ,"NOTES":'               || apex_json.stringify( :P2_NOTES ) ||
            '   ,"ITEMS":'               || apex_json.stringify( :P2_ITEMS ) ||
            '   ,"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 1 )) ||        
            '}' );

        -- 建立SMTP连接
        l_conn := utl_smtp.open_connection(l_smtp_host, l_smtp_port);
        utl_smtp.helo(l_conn, l_smtp_host);
        -- 需SMTP认证时取消下面三行注释
        -- utl_smtp.command(l_conn, 'AUTH LOGIN');
        -- utl_smtp.command(l_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_smtp_user))));
        -- utl_smtp.command(l_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_smtp_pass))));
        
        -- 写入邮件基础头
        utl_smtp.mail(l_conn, '<'||l_sender||'>');
        utl_smtp.rcpt(l_conn, '<'||apex_exec.get_varchar2( l_context, l_emailsidx )||'>');
        utl_smtp.open_data(l_conn);
        utl_smtp.write_data(l_conn, 'From: '||l_sender||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'To: '||apex_exec.get_varchar2( l_context, l_emailsidx )||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Subject: '||utl_encode.mimeheader(l_mail_subject,'UTF8')||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'MIME-Version: 1.0'||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Type: multipart/mixed; boundary="'||l_boundary||'"'||utl_tcp.crlf||utl_tcp.crlf);

        -- 写入HTML正文部分
        utl_smtp.write_data(l_conn, '--'||l_boundary||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Type: text/html; charset=UTF-8'||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Transfer-Encoding: 8bit'||utl_tcp.crlf||utl_tcp.crlf);
        -- 分段写入CLOB内容，避免长度超限
        declare
            l_clob_offset number := 1;
            l_clob_chunk varchar2(32767);
            l_clob_len number := dbms_lob.getlength(l_mail_html);
        begin
            while l_clob_offset <= l_clob_len loop
                l_clob_chunk := dbms_lob.substr(l_mail_html, l_chunk_size, l_clob_offset);
                utl_smtp.write_text(l_conn, l_clob_chunk);
                l_clob_offset := l_clob_offset + l_chunk_size;
            end loop;
        end;
        utl_smtp.write_data(l_conn, utl_tcp.crlf||utl_tcp.crlf);

        -- 写入附件部分
        utl_smtp.write_data(l_conn, '--'||l_boundary||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Type: '||l_attach_mime||'; name="'||l_attach_filename||'"'||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Disposition: attachment; filename="'||l_attach_filename||'"'||utl_tcp.crlf);
        utl_smtp.write_data(l_conn, 'Content-Transfer-Encoding: base64'||utl_tcp.crlf||utl_tcp.crlf);
        
        -- 分段写入Base64编码后的附件
        l_attach_offset := 1;
        while l_attach_offset < dbms_lob.getlength(l_attachment) loop
            dbms_lob.read(l_attachment, l_chunk_size, l_attach_offset, l_raw_attach);
            utl_smtp.write_raw_data(l_conn, utl_encode.base64_encode(l_raw_attach));
            utl_smtp.write_data(l_conn, utl_tcp.crlf);
            l_attach_offset := l_attach_offset + l_chunk_size;
        end loop;

        -- 结束邮件内容，关闭连接
        utl_smtp.write_data(l_conn, utl_tcp.crlf||'--'||l_boundary||'--'||utl_tcp.crlf);
        utl_smtp.close_data(l_conn);
        utl_smtp.quit(l_conn);

        -- 重置临时变量避免循环污染
        dbms_lob.freetemporary(l_mail_html);
    end loop;

    apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
         -- 异常时尝试释放SMTP连接
         begin
             utl_smtp.quit(l_conn);
         exception when others then null;
         end;
     raise; 
end;

补充说明 #

• 如果SMTP服务器需要SSL/TLS加密连接，需要在helo命令后调用UTL_SMTP.starttls初始化加密层，同时提前配置好数据库钱包存储CA证书
• 需要添加多个附件时，重复附件写入段的逻辑，循环读取apex_application_files中对应文件即可
• 单次发送收件人超过50封时，建议每发10封加入1-2秒等待，避免触发SMTP服务器的频率拦截
• 原代码中apex_mail_queue的入队逻辑在UTL_SMTP实现中不需要，调用utl_smtp.quit时邮件会直接完成发送

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