UTL_MAIL Introduction

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/<pwd>
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:[email protected]
250 ok
RCPT TO:[email protected]
250 ok its for <[email protected]>
DATA
354 ok, send it; end with <CRLF>.<CRLF>
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=>'[email protected]',
  3  recipients=>'[email protected]',
  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

blog comments powered by Disqus

Published

23 January 2013

Categories

Tags

Github