Oracle Apex中UTL_SMTP适配TLS 1.2发送邮件失败求助

DevOps 小满

2026-7-19

问题描述

此前在Oracle Apex应用中使用UTL_SMTP发送邮件一切正常，但邮件服务器升级到TLS 1.2后，Oracle包无法正常工作。尝试过添加Wallet等解决方案但均未奏效，还出现‘服务不可用’或‘证书验证失败’等错误。请问该如何解决？是否存在无需TLS认证的邮件服务商？

现有发送邮件PL/SQL包代码

CREATE OR REPLACE PACKAGE BODY send_email
IS
   -- constants
   c_username VARCHAR2 (50) := 'my_sender';
   c_password VARCHAR2 (50) := 'my_password';

   the_connection UTL_SMTP.connection;

   -- Functions
   FUNCTION build_address_string (p_string IN VARCHAR2, p_rcps IN VARCHAR2, p_rcps_names IN VARCHAR2)
      RETURN VARCHAR2
   IS
      i INTEGER;
      v_recipients VARCHAR2 (5000);
      v_reply UTL_SMTP.reply;
   BEGIN
      v_recipients := p_string || p_rcps ;
      
      v_reply := UTL_SMTP.rcpt (the_connection, p_rcps );

      DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| 'UTL_SMTP.rcpt p_recipient');
      DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

      DBMS_OUTPUT.PUT_LINE ( 'v_recipients: '|| v_recipients);

      RETURN v_recipients;
   END;

   -- procedures
   ----------------------------------------------------------------------------------------
   PROCEDURE send_email (p_to_rcps IN VARCHAR2, p_to_rcps_names IN VARCHAR2, p_cc_rcps IN VARCHAR2,
      p_cc_rcps_names IN VARCHAR2, p_subject IN VARCHAR2, p_message_body IN VARCHAR2)
   IS
      i INTEGER;
      v_adr_to VARCHAR2 (5000);
      v_adr_cc VARCHAR2 (5000);
      v_host_name VARCHAR2 (65);
      v_reply UTL_SMTP.reply;
      v_replies UTL_SMTP.replies;
      v_smtp_port NUMBER;
      v_smtp_server VARCHAR2 (100);
      v_smtp_sender VARCHAR2 (100);
      v_smtp_user VARCHAR2 (100);
   BEGIN
      v_host_name := 'the host name';
      v_smtp_server := 'smtp server name';
      v_smtp_port := 25;
      v_smtp_sender := 'email sender';
      v_smtp_user := 'email user';
      
      v_reply := UTL_SMTP.open_connection (v_smtp_server, v_smtp_port, the_connection);

      DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' open connection reply');
      DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

      v_replies := UTL_SMTP.ehlo (the_connection, v_smtp_server);

      i := v_replies.FIRST;

      WHILE (i IS NOT NULL)
      LOOP
          DBMS_OUTPUT.PUT_LINE ( 'count i '|| i|| ' ehlo replies');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply '|| v_replies (i).code);
          DBMS_OUTPUT.PUT_LINE ( 'v_reply '|| v_replies (i).text);

          i := v_replies.NEXT (i);
      END LOOP;

      IF (v_smtp_user != 'ANONYMOUS')
      THEN
          -- BEGIN AUTHENTICATION
          v_reply := UTL_SMTP.command (the_connection, 'AUTH LOGIN'); -- should receive a 334 response, prompting for username

          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' AUTH LOGIN');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

          v_reply := UTL_SMTP.command (the_connection, UTL_ENCODE.text_encode (c_username, 'WE8ISO8859P1', UTL_ENCODE.BASE64)); -- should receive a 334 response, prompting for password

          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' username reply');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

          v_reply := UTL_SMTP.command (the_connection, UTL_ENCODE.text_encode (c_password, 'WE8ISO8859P1', UTL_ENCODE.BASE64)); -- should receive a 235 response, you are authenticated

          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' pwd reply');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
          -- END AUTHENTICATION
      ELSE
          v_reply.code := 235;
      END IF;

      IF (v_reply.code = 235)
      THEN
          -- Check the sender
          v_reply := UTL_SMTP.mail (the_connection, v_smtp_sender);
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.mail sender');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

          -- Creating Adsresses
          v_adr_to := build_address_string ('To: ', p_to_rcps, p_to_rcps_names);
          DBMS_OUTPUT.PUT_LINE ( 'v_adr_to: '|| v_adr_to);

          v_adr_cc := build_address_string ('Cc: ', p_cc_rcps, p_cc_rcps_names);
          DBMS_OUTPUT.PUT_LINE ( 'v_adr_cc: '|| v_adr_cc);

          -- Writing the data
          v_reply := UTL_SMTP.open_data (the_connection);
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.open_data ');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);

          UTL_SMTP.write_data(the_connection, 'From: ' || v_host_name || UTL_TCP.crlf);
          UTL_SMTP.write_data(the_connection, 'Subject: ' || NVL (p_subject, '(no subject)') || UTL_TCP.crlf);
          --UTL_SMTP.write_data(the_connection, 'Reply-To: ' || v_adr_to || UTL_TCP.crlf || UTL_TCP.crlf);
          UTL_SMTP.write_data(the_connection, v_adr_to || UTL_TCP.crlf);
          UTL_SMTP.write_data(the_connection, v_adr_cc || UTL_TCP.crlf);
          UTL_SMTP.write_data (the_connection, '' || UTL_TCP.crlf);
          UTL_SMTP.write_data(the_connection, p_message_body || UTL_TCP.crlf || UTL_TCP.crlf);

          -- sending email and closing the connection
          UTL_SMTP.close_data (the_connection);

          v_reply := UTL_SMTP.quit (the_connection);
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.quit');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
      ELSE
          DBMS_OUTPUT.PUT_LINE ( 'authentication failure ');

          v_reply := UTL_SMTP.quit (the_connection);
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.quit');
          DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
      END IF;

   EXCEPTION
      WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
      THEN
          BEGIN
              UTL_SMTP.quit (the_connection);
          EXCEPTION
              WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
              THEN
                  RAISE;
          -- have a connection to the server. The quit call will
          -- raise an exception that we can ignore.
          END;

          raise_application_error (-20000, 'Failed to send mail due to the following error: ' || SQLERRM);
      WHEN OTHERS
      THEN
          RAISE;
   END send_email;
END send_email;
/

解决方法

一、适配TLS 1.2的核心调整

1. 确认Oracle版本兼容性

Oracle 11gR2：需安装补丁19034276及后续相关补丁，才能支持TLS 1.2。
Oracle 12c及以上版本：默认支持TLS 1.2，但需确认数据库配置是否启用。

2. 正确配置Wallet与SQLNET.ORA

Wallet创建与证书导入：使用orapki工具创建Wallet，将邮件服务器的根证书导入其中。确保证书链完整，避免因中间证书缺失导致验证失败。

SQLNET.ORA配置示例：

WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /path/to/wallet)))
SQLNET.AUTHENTICATION_SERVICES = (TCPS, BEQ)
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256, SSL_RSA_WITH_AES_128_CBC_SHA256)
SSL_SERVER_DN_MATCH = NO

注：SSL_SERVER_DN_MATCH=NO可临时绕过证书DN匹配（仅测试用，生产环境不建议）。

3. 修改UTL_SMTP连接逻辑

原代码未启用TLS加密，需调整连接方式：

方式1：使用STARTTLS（端口587）

在EHLO之后发送STARTTLS命令，切换到加密连接，再重新执行EHLO和认证：

-- 在EHLO之后添加以下代码
v_reply := UTL_SMTP.command(the_connection, 'STARTTLS');
IF v_reply.code != 220 THEN
    raise_application_error(-20001, 'STARTTLS failed: ' || v_reply.text);
END IF;
-- 重新执行EHLO以获取加密后的服务器能力
v_replies := UTL_SMTP.ehlo(the_connection, v_smtp_server);

同时将v_smtp_port改为587。

方式2：直接使用SSL连接（端口465）

使用UTL_SMTP.OPEN_CONNECTION的SSL参数：

v_reply := UTL_SMTP.open_connection(
    host => v_smtp_server,
    port => 465,
    c => the_connection,
    use_ssl => TRUE,
    wallet_path => 'file:/path/to/wallet',
    wallet_password => 'wallet_password'
);

此方式需将端口改为465，并传入Wallet路径和密码。

4. 检查端口与防火墙

确保Oracle服务器能访问邮件服务器的TLS端口（587或465），防火墙未拦截流量。

二、关于无需TLS认证的邮件服务商

目前主流公共邮件服务商（如Gmail、Outlook、阿里云邮箱等）均强制要求TLS加密，不再支持纯明文传输。若需非TLS环境，仅能选择：

私有搭建的邮件服务器：自行部署Postfix、Exim等邮件服务，配置为允许明文传输（但存在数据泄露风险，不推荐生产使用）。
本地中转服务器：在Oracle服务器本地搭建轻量邮件服务器，先以明文接收Oracle的邮件，再通过TLS转发到公共服务商（需权衡安全与复杂度）。

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

Oracle Apex中UTL_SMTP适配TLS 1.2发送邮件失败求助

一、适配TLS 1.2的核心调整 #

1. 确认Oracle版本兼容性 #

2. 正确配置Wallet与SQLNET.ORA #

3. 修改UTL_SMTP连接逻辑 #