Oracle 利用 UTL_SMTP 包发送邮件

是这样子，上周末的时候逛了下itpub，看到个挺好玩的东西，通过Oracle的UTL_SMTP包的功能，用定时任务检查数据库状态并发送邮件，因为之前没用过这个包，不知道还有这么好玩的功能，特记录分享一下发邮件的这部分配置。

这里原文：oracle数据库自动发邮件实现报警功能

用处还是蛮多的，想干嘛就干嘛~

一、说明一下

我们是通过建一个存储过程的方式去处理邮件发送这个事情，配置的参数，大概是需要知道4个东西，需要获取到SMTP服务器地址，登录SMTP服务器的用户名，登录SMTP服务器的密码，发送者邮箱。就例如我是用的我的QQ邮箱，下面说明一下怎么获取。

1、SMTP服务器地址，QQ邮箱的话百度一下就知道，smip服务器地址是 smtp.qq.com，顺便可以知道端口号是465。

2、登录SMTP服务器的用户名，QQ邮箱的话，就是你的邮箱地址，例如我用的就是 21******97@qq.com。

3、登录SMTP服务器的密码，这个需要注意下，例如QQ邮箱的这个密码，并不是QQ密码，进入你的QQ邮箱，设置——>账户——>POP3/SMTP服务，通过短信验证获取一个授权码，就是这个东西了。

4、发送者邮箱，同2，也就是你的邮箱

二、存储过程

CREATE OR REPLACE PROCEDURE p_send_email(p_recipient VARCHAR2, -- 邮件接收人
                                         p_subject VARCHAR2, -- 邮件标题
                                         p_message VARCHAR2 -- 邮件正文
)
IS
  -- 下面四个变量请根据实际邮件服务器进行赋值
  v_mailhost VARCHAR2(30) := 'smtp.qq.com'; -- SMTP服务器地址
  v_user VARCHAR2(30) := '21******97@qq.com'; -- 登录SMTP服务器的用户名
  v_pass VARCHAR2(20) := 'abcdefghijklmmnopqrstuvwxyz'; -- 登录SMTP服务器的密码
  v_sender VARCHAR2(50) := '21******97@qq.com'; -- 发送者邮箱，一般与 v_user 一样
  ---------------- 配置完成 -----------------

  v_conn UTL_SMTP.connection; -- 到邮件服务器的连接
  v_msg VARCHAR2(4000); -- 邮件内容
BEGIN
  v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
  UTL_SMTP.ehlo(v_conn, v_mailhost);
  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 || '>'); --设置收件人
  -- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
  v_msg := 'Date:' || TO_CHAR(SYSDATE, 'yyyy mm dd 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 p_send_email;
/

三、还要配置点东西

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

下面需要修改一下数据库用户 principal，非qq邮箱的还需改一下smtp服务器地址和端口号

--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    => 'LIHY',    -- 配置下使用的数据库用户
  is_grant     => TRUE, 
  privilege    => 'connect');
END;
/

--2. 将此 ACL 与邮件服务器相关联，
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'email_server_permissions.xml',
    host        => 'smtp.qq.com', --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    => 'LIHY',  -- 配置下使用的数据库用户
  is_grant     => TRUE, 
  privilege    => 'connect');
END;
/

--4. 将此 ACL 与邮件服务器相关联，
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'network_services.xml',
    host        => 'smtp.qq.com', --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 p_send_email('发送给哪个邮箱', '邮件的标题', '邮件内容');

--当然，你的数据库服务器需要能连外网
--有报错正常，自己看下输出的错误找度娘解决

走一个，dang dang dang dang ~