oracle通过UTL_SMTP包发送邮件

oracle通过UTL_SMTP包发送邮件

Oracle的UTL_SMTP包可以实现邮件发送功能，需要高权限用户编译出来的才能运行

所需条件：

• SMTP服务器地址
• 登录SMTP服务器的用户名
• 登录SMTP服务器的密码

存储过程代码

CREATE OR REPLACE PROCEDURE send_mail(
       p_recipient VARCHAR2, -- 邮件接收人
       p_subject   VARCHAR2, -- 邮件标题
       p_message   VARCHAR2  -- 邮件正文
 )

IS
      --下面四个变量请根据实际邮件服务器进行赋值
      v_mailhost  VARCHAR2(30) := 'mail.xxxx.net';    --SMTP服务器地址
      v_user      VARCHAR2(30) := 'user@xxxx.net';    --登录SMTP服务器的用户名
      v_pass      VARCHAR2(20) := '******';           --登录SMTP服务器的密码
      v_sender    VARCHAR2(50) := 'user@xxxx.net';    --发送者邮箱，一般与 v_user 对应
      v_conn  UTL_SMTP.connection ; --到邮件服务器的连接
      v_msg varchar2(4000);  --邮件内容
 BEGIN
      v_conn := UTL_SMTP.open_connection(v_mailhost, 25);-- 登录smtp服务器，use端口
      UTL_SMTP.ehlo(v_conn, v_mailhost); --是用 ehlo() 而不是 helo() 函数
      --否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.
      UTL_SMTP.command(v_conn, 'AUTH LOGIN' );   -- smtp服务器登录校验
      UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
      UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));
      UTL_SMTP.mail(v_conn, '<'|| v_sender ||'>');     --设置发件人
      UTL_SMTP.rcpt(v_conn, '<'||p_recipient||'>');  --设置收件人
      -- 解决: utl_smtp.mail和utl_smtp.rcpt发送和接收的地址前后需要加上'<'和'>'  否则会报 ORA-29279: SMTP 永久性错误: 550 Invalid User
      -- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
      v_msg := 'Date:' || TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss' )
          || UTL_TCP.CRLF || 'From: ' || '<' || v_sender || '>'
          || UTL_TCP.CRLF || 'To: ' || '<' || p_recipient || '>'
          || UTL_TCP.CRLF || 'Subject: ' || p_subject
          || UTL_TCP.CRLF || UTL_TCP.CRLF  -- 这前面是报头信息
          || p_message;    -- 这个是邮件正文
      UTL_SMTP.open_data(v_conn); --打开流
      UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg)); --这样写标题和内容都能用中文
      UTL_SMTP.close_data(v_conn); --关闭流
      UTL_SMTP.quit(v_conn); --关闭连接
 EXCEPTION
      WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
          DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
 END send_mail;

数据库服务器配置

按上面的配置好并创建好存储过程之后，还需要配置一下Oracle的访问控制列表并和邮件服务器关联

--1.创建访问控制列表（aclemail_server_permissions），
begin
 dbms_network_acl_admin.create_acl (
  acl          => 'email_server_permissions.xml', 
  description  => 'Enables network permissions for the e-mail server',
  principal    => 'ICG',    -- 配置下使用的数据库用户
  is_grant     => true, 
  privilege    => 'connect');
end;

--1.1. 若已存在，可先删除再新建
begin 
  dbms_network_acl_admin.drop_acl(acl => 'email_server_permissions.xml');
end;
  


--2. 将此 acl 与邮件服务器相关联，
begin
dbms_network_acl_admin.assign_acl (
    acl         => 'email_server_permissions.xml',
    host        => 'mail.icgear.net', --smtp服务器地址
    lower_port  => 465,
    upper_port  => 465);
  commit;
end;

 
--3.创建访问控制列表（acl）network_services，
begin
 dbms_network_acl_admin.create_acl (
  acl          => 'network_services.xml', 
  description  => 'Enables network permissions for the e-mail server',
  principal    => 'ICG',  -- 配置下使用的数据库用户
  is_grant     => true, 
  privilege    => 'connect');
end;

--3.1. 若已存在，可先删除再新建
begin 
  dbms_network_acl_admin.drop_acl(acl => 'network_services.xml');
end;
  

--4. 将此 acl 与邮件服务器相关联，
begin
dbms_network_acl_admin.assign_acl (
    acl         => 'network_services.xml',
    host        => 'mail.icgear.net', --smtp服务器地址
    lower_port  => null,
    upper_port  => null);
  commit;
end;


--上面4个配置完成后可以查一下是否都能查到
select host, lower_port, upper_port, acl from dba_network_acls;
 
select acl,
   principal,
   privilege,
   is_grant,
   to_char(start_date, 'DD-MON-YYYY') as start_date,
   to_char(end_date, 'DD-MON-YYYY') as end_date
from dba_network_acl_privileges;

正常情况下，数据库服务器配置好之后即可发送邮件了

   call send_mail('xxx@xxxx.net','test','邮件测试');

常见异常

如果上述配置完成后还是不可以正常发送邮件

异常1-ORA-29278

ORA-29278: SMTP 临时性错误: 421 Service not available

解决方法：

1. 检查SMTP服务器地址填写是否正确
2. 检查防火墙是否有禁用【25】【465】等邮件发送相关端口
3. 如果是服务器，有些服务器可能没配置上网功能，检查DNS等网络配置是否正常

---

本文来源：https://blog.youkuaiyun.com/Huay_Li/article/details/84423734