--- layout: post title: UTL_MAIL Introduction category : Oracle tags : [Oracle, Database, DBA] --- ## UTL_MAIl reference ### Security Model `UTL_MAIL` is not installed by default because of the `SMTP_OUT_SERVER ` configuration requirement and the security exposure this involves. In installing `UTL_MAIL`, you should take steps to prevent the port defined by `SMTP_OUT_SERVER ` being swamped by data transmissions. This package is now an invoker's rights package and the invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect. ### Operations You must both install `UTL_MAIL` and define the `SMTP_OUT_SERVER`. ■ To install UTL_MAIL: sqlplus sys/ SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb ■ You define the `SMTP_OUT_SERVER` parameter in the init.ora rdbms initialization file. However, if `SMTP_OUT_SERVER` is not defined, this invokes a default of `DB_DOMAIN` which is guaranteed to be defined to perform appropriately ### Rules and Limits Use `UTL_MAIL` only within the context of the ASCII (American Standard Code for Information Interchange) and EBCDIC (Extended Binary-Coded Decimal Interchange Code) codes. ### Summary of UTL_MAIL * SEND：Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients * SEND_ATTACH_RAW：Represents the SEND Procedure overloaded for RAW attachments * SEND_ATTACH_VARCHAR2：Represents the SEND Procedure overloaded for VARCHAR2 attachments ## UTL_MAIL in Action ### 安装UTL_MAIL包切换到Oracle身份，以sys用户登录： [oracle@oradb]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 23 17:23:38 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> desc UTL_MAIL.SEND ERROR: ORA-04043: object UTL_MAIL.SEND does not exist 正如前面提到的, `UTL_MAIL`包在安装数据库时并不会默认安装；此时需要手动安装，安装过程如下：切换到`UTL_MAIL`包路径，确认代码是否存在： [oracle@oradb admin]$ cd $ORACLE_HOME/rdbms/admin [oracle@oradb admin]$ pwd /db/oracle/product/11.2.0/db_1/rdbms/admin [oracle@oradb admin]$ ls *mail* prvtmail.plb utlmail.sql 安装`UTL_MAIL`包： [oracle@oradb admin]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 23 17:46:24 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> @utlmail.sql Package created. Synonym created. SQL> @prvtmail.plb Package created. Package body created. Grant succeeded. Package body created. No errors. SQL> desc utl_mail PROCEDURE SEND Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER VARCHAR2 IN RECIPIENTS VARCHAR2 IN CC VARCHAR2 IN DEFAULT BCC VARCHAR2 IN DEFAULT SUBJECT VARCHAR2 IN DEFAULT MESSAGE VARCHAR2 IN DEFAULT MIME_TYPE VARCHAR2 IN DEFAULT PRIORITY BINARY_INTEGER IN DEFAULT REPLYTO VARCHAR2 IN DEFAULT PROCEDURE SEND_ATTACH_RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER VARCHAR2 IN RECIPIENTS VARCHAR2 IN CC VARCHAR2 IN DEFAULT BCC VARCHAR2 IN DEFAULT SUBJECT VARCHAR2 IN DEFAULT MESSAGE VARCHAR2 IN DEFAULT MIME_TYPE VARCHAR2 IN DEFAULT PRIORITY BINARY_INTEGER IN DEFAULT ATTACHMENT RAW IN ATT_INLINE BOOLEAN IN DEFAULT ATT_MIME_TYPE VARCHAR2 IN DEFAULT ATT_FILENAME VARCHAR2 IN DEFAULT REPLYTO VARCHAR2 IN DEFAULT PROCEDURE SEND_ATTACH_VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER VARCHAR2 IN RECIPIENTS VARCHAR2 IN CC VARCHAR2 IN DEFAULT BCC VARCHAR2 IN DEFAULT SUBJECT VARCHAR2 IN DEFAULT MESSAGE VARCHAR2 IN DEFAULT MIME_TYPE VARCHAR2 IN DEFAULT PRIORITY BINARY_INTEGER IN DEFAULT ATTACHMENT VARCHAR2 IN ATT_INLINE BOOLEAN IN DEFAULT ATT_MIME_TYPE VARCHAR2 IN DEFAULT ATT_FILENAME VARCHAR2 IN DEFAULT REPLYTO VARCHAR2 IN DEFAULT ### 给用户授权非SYS用户没有权限调用`UTL_MAIL`: SQL> disc Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn dev Enter password: Connected. SQL> desc utl_mail ERROR: ORA-04043: object "SYS"."UTL_MAIL" does not exist 授权给PUBLIC或某一个特定的用户，以使用户有权限调用该程序包： SQL> conn /as sysdba Connected. SQL> grant execute on utl_mail to dev; Grant succeeded. 确认授权后，用户可以访问该程序包： SQL> conn dev Enter password: Connected. SQL> desc utl_mail PROCEDURE SEND Argument Name Type In/Out Default? ... ... ### 邮件服务器测试 #### 使用telnet测试smtp是否正常 [oracle@oradb ]$ telnet smtp.test-it.net 25 Trying 192.168.1.111... Connected to smtp.test-it.net. Escape character is '^]'. 220 test-it.net (IMail 9.23 574278-2) NT-ESMTP Server X1 EHLO oradb.test-it.net 250-test-it.net says hello 250-SIZE 0 250-8BITMIME 250-DSN 250-ETRN 250-AUTH LOGIN CRAM-MD5 250-AUTH LOGIN 250-AUTH=LOGIN 250 EXPN MAIL FROM:dylanninin@test-it.net 250 ok RCPT TO:anyone@test-it.net 250 ok its for DATA 354 ok, send it; end with . Subject:telnet smtp test This is a test message with telnet smtp . 250 Message queued quit 221 Goodbye Connection closed by foreign host. 查看邮件箱，或者`/var/mail/log`确保可以收到以上邮件。若邮件服务有问题，需先配置好，才能进行`UTL_MAIL`的测试。 ### UTL_MAIl使用示例 ### 发送第一封邮件 SQL> conn dev Enter password: Connected. SQL> begin 2 utl_mail.send(sender=>'dylanninin@test-it.net', 3 recipients=>'anyone@test-it.net', 4 subject=>'utl_mail smtp test', 5 message=>'This is a test message with utl_mail'); 6 end; 7 / begin * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL", line 671 ORA-06512: at line 2 出现以上错误，是因为`smtp_out_server`没有设置。设置`smtp_out_server`参数： SQL> conn /as sysdba Connected. SQL> show parameter smtp_out_server NAME TYPE VALUE ------------------------ ----------- --------------------------- smtp_out_server string SQL> alter system set smtp_out_server='smtp.it-test.net'; System altered. 发送以上同样的邮件，出现新的错误： ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL", line 671 ORA-06512: at line 2 查看MOS，原来是Oracle做了ACL限制，需要授权允许用户DEV访问外部网络。执行以下脚本，授权DEV访问外部网络(若是其他用户，则将DEV改为对应账户即可)： BEGIN -- Only uncomment the following line if ACL "network_services.xml" has already been created --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml'); DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'network_services.xml', description => 'SMTP ACL', principal => 'DEV', is_grant => true, privilege => 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'network_services.xml', principal => 'DEV', is_grant => true, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'network_services.xml', host => '*'); COMMIT; END; / 执行后，重新运行上述发邮件代码，运行无错误，查看收件箱也可以成功收到邮件。到此，使用`UTL_MAIL`就成功发送了第一封邮件。 ## Reference * [PL/SQL Packages and Types Reference](http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258) * Master Note For PL/SQL `UTL_SMTP` and `UTL_MAIL` Packages [ID 1137673.1]