Oracle 11g 使用 UTL_MAIL 包实现存储过程发送邮件

在 oracle 开发或维护时，我们经常需要用到发送邮件来实现预警功能。在 oracle 10g 之前，我们采用 utl_smtp 来包装为一个邮件的方式来发送邮件，这种方式需要写比较长的代码；在 oracle 10g 之后，我们可以直接调用 utl_mail 包来发送邮件，非常简单。

1、简介并安装 UTL_MAIL 包

[plain] view plain copy

 print?

1. The UTL_MAIL package is a utility for managing email which includes commonly used email features,   
2. such as attachments, CC, BCC, and return receipt.  
3.   
4. UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement   
5. and the security exposure this involves.  
6.   
7. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER   
8. being swamped by data transmissions.  

UTL_MAIL 包是一个用来发送邮件的工具，包括常用的电子邮件功能，比如附件，抄送，密送以及回执。由于 SMTP_OUT_SERVER 配置的要求以及安全隐患问题，默认情况下 UTL_MAIL 包是没有安装到Oracle 中的。所以如果需要使用就要额外进行安装。

安装的方法是在 sys 用户下，调用$ORACLE_HOME/rdbms/admin目录下的两个脚本文件。

[plain] view plain copy

 print?

1. SQL> connect / as sysdba;  
2. Connected.  
3.    
4. SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql  
5.    
6. Package created.  
7. Synonym created.  
8.    
9. SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb  
10.    
11. Package created.  
12. Package body created.  
13. Grant succeeded.  
14. Package body created.  
15.    
16. No errors.  

这两个脚本的作用就是建立utl_mail工具包。安装完成之后要设置 smtp_out_server 参数。该参数指定的是连接邮件服务器的名称或 IP 地址。

[plain] view plain copy

 print?

1. SQL> show parameter smtp  
2.    
3. NAME                                 TYPE        VALUE  
4. ------------------------------------ ----------- ---------------  
5. smtp_out_server                      string  
6.   
7. SQL> alter system set smtp_out_server='192.10.2.55' scope=both;  
8. System altered.  
9.    
10. SQL> show parameter smtp  
11. NAME                                 TYPE        VALUE  
12. ------------------------------------ ----------- ---------------  
13. smtp_out_server                      string      192.10.2.55  

utl_mail  包包含三个方法(send, send_attach_raw, send_attach_varchar2)

[plain] view plain copy

 print?

1. SEND Procedure  
2. Packages an email message into the appropriate format, locates SMTP information,   
3. and delivers the message to the SMTP server for forwarding to the recipients  
4.   
5. SEND_ATTACH_RAW Procedure  
6. Represents the SEND Procedure overloaded for RAW attachments  
7.   
8. SEND_ATTACH_VARCHAR2 Procedure  
9. Represents the SEND Procedure overloaded for VARCHAR2 attachments  

如果邮件不用附件的话，我们使用 send 方法就可以了

2、使用 SYS 用户直接调用 utl_mail 包的 send 方法

[plain] view plain copy

 print?

1. UTL_MAIL.SEND (  
2.    sender      IN    VARCHAR2 CHARACTER SET ANY_CS,  
3.    recipients  IN    VARCHAR2 CHARACTER SET ANY_CS,  
4.    cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,  
5.    bcc         IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,  
6.    subject     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,  
7.    message     IN    VARCHAR2 CHARACTER SET ANY_CS,  
8.    mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',  
9.    priority    IN    PLS_INTEGER DEFAULT NULL);  

具体参数解释如下：

[plain] view plain copy

 print?

1. sender      The email address of the sender.  
2. recipients  The email addresses of the recipient(s), separated by commas.  
3. cc      The email addresses of the CC recipient(s), separated by commas, default is NULL  
4. bcc     The email addresses of the BCC recipient(s), separated by commas, default is NULL  
5. subject     A string to be included as email subject string, default is NULL  
6. message     A text message body.  
7. mime_type   The mime type of the message, default is 'text/plain; charset=us-ascii'  
8. priority    The message priority, default is NULL.  

下面在sys下使用utl_mail.send方法发送邮件。

[plain] view plain copy

 print?

1. begin  
2.   utl_mail.send(sender => '[e-mail]',  
3.                 recipients => '[e-mail]',  
4.                 message => 'abcccc',  
5.                 subject => 'ddddd');  
6. end;  
7. /  
8.   
9. PL/SQL procedure successfully completed  

在接受邮箱中发现发送的邮件。

 

3、非 SYS 用户调用 utl_mail 包的 send 方法配置

 

如果是非 SYS 用户直接使用utl_mail包，需要将 utl_tcp、utl_mail、utl_smtp 和 dbms_network_acl_admin 四个包的执行权限赋给该用户。

[plain] view plain copy

 print?

1. SQL> grant execute on utl_tcp to ava  
2. Grant succeeded.  
3.    
4. SQL> grant execute on utl_smtp to ava  
5. Grant succeeded.  
6.    
7. SQL> grant execute on utl_mail to ava  
8. Grant succeeded.  
9.    
10. SQL> grant execute on dbms_network_acl_admin to ava  
11. Grant succeeded.  

此外由于不是超级用户(sys), 所以没有权限去访问外部网络服务(External network service)，因此需要配置 Access Control List(ACL)

[plain] view plain copy

 print?

1. To configure fine-grained access to external network services, you create an access control list (ACL),   
2. which is stored in Oracle XML DB. You can create the access control list by using Oracle XML DB itself,   
3. or by using the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY PL/SQL packages.   
4. This guide explains how to use these packages to create and manage the access control list.   
5. To create an access control list by using Oracle XML DB and for general conceptual information   
6. about access control lists, see Oracle XML DB Developer's Guide.  

[plain] view plain copy

 print?

1. Begin  
2.   //设置权限项目  
3.   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (acl => 'email_server_permissions.xml',  
4.                         description => 'Enables network permissions for the e-mail server',  
5.                                      principal => 'AVA',  
6.                                      is_grant => TRUE,  
7.                                      privilege => 'connect');  
8. end;  
9. /  
10.    
11. BEGIN  
12.    //指定访问主机和相应端口；  
13.    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'email_server_permissions.xml',  
14.                                       host => '192.10.2.55',  
15.                                       lower_port => 25);  
16. END;  
17. /  

之后再使用发送邮件方法

[plain] view plain copy

 print?

1. begin  
2.   utl_mail.send(sender => '[e-mail]',  
3.                 recipients => '[e-mail]',  
4.                 message => 'abcccc',  
5.                 subject => 'ddddd');  
6. end;  
7. /  
8.   
9. PL/SQL procedure successfully completed  

在接受邮箱中发现发送的邮件。

4、封装成一个存储过程调用

[plain] view plain copy

 print?

1. CREATE OR REPLACE PROCEDURE EMAIL_MIS(p_message IN varchar2, p_subject IN varchar2) IS  
2. v_sender varchar2(100);  
3. v_receive varchar2(100);  
4. Begin  
5.     v_sender := '[e-mail]';  
6.     v_receive := '[e-mail], [e-mail]';  
7.     utl_mail.send(sender => v_sender,  
8.                 recipients => v_receive,  
9.                 message => p_message,  
10.                 subject => p_subject);  
11. EXCEPTION  
12.    WHEN OTHERS  
13.    THEN  
14.        DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);     
15.       DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);  
16. END EMAIL_MIS;  

以后可以直接调用存储过程 EMAIL_MIS 就可以了。