使用oracle job定时发送email邮件

最近项目中涉及到了使用oracle job定时发送email邮件，这里记录下实施过程。

1.开通ACL权限

oracle11g，在网络权限控制上提出的ACL（Access Control List），是一种细粒度的权限控制。而我们发送邮件涉及的程序包如UTL_MAIL，UTL_SMTP都需要打通网络权限。
首先我们使用sys登录oracle，我一般是开了个test窗口，在test窗口中执行下面的sql：

dbms_network_acl_admin.create_acl(acl         => 'email.xml', --xml名称
                                      DESCRIPTION => 'email', 		  --描述
                                      principal   => 'GBMIS_KL',    --数据库名
                                      is_grant    => TRUE,
                                      PRIVILEGE   => 'connect',	--权限名
                                      start_date  => NULL,
                                      end_date    => NULL);

2.添加权限接口

 dbms_network_acl_admin.add_privilege(acl        => 'email.xml', --同上xml名称
                                         principal  => 'GBMIS_KL', --数据库名
                                         is_grant   => TRUE,
                                         privilege  => 'connect', --权限名
                                         start_date => null,
                                         end_date   => null);

3.注册ACL（注意commit提交）

dbms_network_acl_admin.assign_acl (       -- 该段命令意思是允许访问acl名为utl_sendmail.xml下授权的用户，使用oracle网络访问包，所允许访问的目的主机，及其端口范围。
    acl        => 'email.xml',
    host       => '*'-- ,              -- ip地址或者域名，填写http://localhost:9000/hello与http://localhost:9000/是会报host无效的
                                            -- 且建议使用ip地址或者使用域名，若用localhost，当oracle不是安装在本机上的情况下，会出现问题
   -- lower_port => 9000,                     -- 允许访问的起始端口号
   -- upper_port => Null                      -- 允许访问的截止端口号
  );

执行完成后，我们可以执行下面的sql来查询权限：

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;

 Select * From  dba_network_acls;

4.创建发送邮件存储过程

procedure send_mail(p_recipient VARCHAR2, -- 邮件接收人
                      p_subject   VARCHAR2, -- 邮件标题
                      p_message   VARCHAR2, -- 邮件正文
                      p_type      number -- 1文本  2html
                      ) as

    --下面四个变量请根据实际邮件服务器进行赋值
    v_mailhost VARCHAR2(30) := 'smtp.qq.com'; --SMTP服务器地址
    v_user     VARCHAR2(30) := '10445242@qq.com'; --登录SMTP服务器的用户名
    v_pass     VARCHAR2(20) := 'yjtsmqcxvstrcaeg'; --登录SMTP服务器的密码 授权码
    v_sender   VARCHAR2(50) := '10445242@qq.com'; --发送者邮箱，一般与 ps_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); --是用 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 || '>'); --设置发件人 注：网上很多资料直接写v_sender，这样写会报ORA-29279: SMTP 500 error
    UTL_SMTP.rcpt(v_conn, '<' || p_recipient || '>'); --设置收件人

    UTL_SMTP.open_data(v_conn); --打开流
    if p_type = 1 then
      -- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
      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.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg)); --这样写标题和内容都能用中文
    elsif p_type = 2 then
      UTL_SMTP.write_data(v_conn,
                          'From:' || '<' || v_sender || '>' || utl_tcp.CRLF);
      UTL_SMTP.write_data(v_conn,
                          'To:' || '<' || p_recipient || '>' ||
                          utl_tcp.crlf);
      UTL_SMTP.write_raw_data(v_conn,
                              UTL_RAW.cast_to_raw(convert('Subject:' ||
                                                          p_subject ||
                                                          utl_tcp.CRLF,
                                                          'ZHS16GBK')));

      UTL_SMTP.write_raw_data(v_conn,
                              UTL_RAW.cast_to_raw(convert('Content-Type:text/html;charset=GBK' ||
                                                          utl_tcp.CRLF,
                                                          'ZHS16GBK')));
      UTL_SMTP.write_data(v_conn, utl_tcp.CRLF);
      UTL_SMTP.write_raw_data(v_conn,
                              UTL_RAW.cast_to_raw(convert(p_message,
                                                          'ZHS16GBK'))); --这样写标题和内容都能用中文

    end if;

    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;

5.设置oracle——job

这个是oracle提供的定时功能，类似于js中的setInterval()。

DECLARE  
X NUMBER;  
BEGIN  
SYS.DBMS_JOB.SUBMIT  
( job => X  
,what => 'pkg_interface.job_findConfigEmail;'  
,next_date => sysdate  
,interval => 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'  
,no_parse => TRUE  
);  
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));  
COMMIT;  
END;  
/ 

可以查看当前用户job列表

select * from user_jobs;

也可以终止该job

exec dbms_job.broken(244,true) --244代表该job的id