Oracle存储过程发生异常时未触发UTL_SMTP邮件发送问题排查

问题原因排查及解决方法 #

常见触发原因

1. ACL权限不足
   Oracle 11g及以上版本引入了网络访问控制列表（ACL），默认禁止普通用户调用UTL_SMTP、UTL_TCP等网络包访问外部服务，你当前的存储过程所属用户UPDATER大概率没有配置访问邮件服务器25端口的ACL权限，调用UTL_SMTP.open_connection时会直接触发新异常，跳出当前异常处理块，导致邮件发送逻辑中断。
2. 发邮件逻辑无二次异常捕获
   你在异常处理块中直接写了发邮件的逻辑，但没有对发邮件的过程单独做异常捕获，如果发邮件过程中出现任何错误（比如连不上服务器、认证失败），新的异常会直接终止整个存储过程，你也看不到发邮件环节的具体报错信息。
3. 变量未赋值或长度不足
   你代码中用到的err_message没有在异常块中提前赋值，且如果该变量定义的长度过短，拼接错误信息时会触发你遇到的ORA-06502字符转数字/值错误，反而中断了后续逻辑。你报错的位置是存储过程第19行，可以先核对该行是否为err_message相关的赋值/调用代码。
4. 邮件服务器配置或网络问题

• 邮件服务器地址v_Mail_Host配置错误，或数据库所在服务器无法连通邮件服务器的25端口（被防火墙、网络策略拦截）
• 邮件服务器要求SMTP身份认证，但你当前代码没有加认证逻辑，被服务器拒绝发信

修复步骤

• 第一步：调整异常处理逻辑，增加发邮件环节的异常捕获，补充变量赋值
  修改后的异常块参考代码：

EXCEPTION
  WHEN OTHERS THEN
  -- 先给错误信息变量赋值，建议定义为VARCHAR2(4000)避免长度不足
  err_message := '错误栈信息：' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || CHR(10) || '错误详情：'||sqlerrm;
  
  -- 发邮件逻辑嵌套异常捕获，避免发信失败中断整个流程
  BEGIN
    v_mail_conn2 := UTL_SMTP.open_connection(v_Mail_Host, 25);
    UTL_SMTP.helo(v_mail_conn2, v_Mail_Host);
    
    -- 如果邮件服务器需要SMTP认证，放开下面三行注释，替换成你自己的SMTP用户名密码
    -- UTL_SMTP.command(v_mail_conn2, 'AUTH LOGIN');
    -- UTL_SMTP.command(v_mail_conn2, UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('你的SMTP用户名')));
    -- UTL_SMTP.command(v_mail_conn2, UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('你的SMTP密码')));
    
    UTL_SMTP.mail(v_mail_conn2, v_From);
    UTL_SMTP.rcpt(v_mail_conn2, v_Recipient);
    UTL_SMTP.open_data(v_mail_conn2);
    
    UTL_SMTP.write_data(v_mail_conn2, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_mail_conn2, 'To: ' || v_Recipient || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_mail_conn2, 'From: ' || v_From || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_mail_conn2, 'Subject: ' || v_Subject || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_mail_conn2, err_message || UTL_TCP.crlf);
    
    UTL_SMTP.close_data(v_mail_conn2);
    UTL_SMTP.quit(v_mail_conn2);
  EXCEPTION
    WHEN OTHERS THEN
      -- 打印发信环节的错误，方便排查
      DBMS_OUTPUT.put_line('邮件发送失败，错误信息：'||sqlerrm);
  END;

  DBMS_OUTPUT.put_line ( '业务逻辑执行错误: '|| err_message);
END UNLOCK_AND_EMAIL_DUMP;

• 第二步：配置ACL网络权限
  用SYS用户登录数据库，执行以下语句给UPDATER用户开放邮件服务器访问权限：

BEGIN
  -- 创建ACL规则
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'smtp_access.xml',
    description => '允许访问SMTP邮件服务器',
    principal => 'UPDATER', -- 替换为你的存储过程所属用户名，注意大写
    is_grant => TRUE,
    privilege => 'connect'
  );
  -- 绑定要访问的邮件服务器地址和端口
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'smtp_access.xml',
    host => '你的邮件服务器地址', -- 替换为v_Mail_Host的实际值
    lower_port => 25,
    upper_port => 25
  );
  COMMIT;
END;
/

• 第三步：排查网络连通性
  登录到Oracle数据库所在的服务器，执行telnet 你的邮件服务器地址 25，确认可以正常连通，如果不通先排查服务器防火墙、出口网络策略是否放通了25端口的访问。
• 第四步：确认邮件服务器认证要求
  如果你的邮件服务器要求SMTP账号密码认证，要在代码中补充认证逻辑，否则服务器会直接拒绝发信请求。

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