This is true on 10g and 11g. To install
@?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plbscripts should be run as sys and
smtp_out_serverparameter should be set on the spfile with mail_server_ip:port format. "However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately" (Oracle PL/SQL Guide) and when utl_mail is invoked without smtp_out_server set
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.UTL_MAIL", line 427 ORA-06512: at "SYS.UTL_MAIL", line 664 ORA-06512: at line 1There are some differences in the configuration before using of utl_mail in 10g and 11g where 11g's enhanced security features such as access control list (ACL) plays a major part in the configuration.
First sending an email using utl_mail on a 10.2.0.5.0 standard edition databases.
SQL> @?/rdbms/admin/utlmail Package created. Synonym created. SQL> @?/rdbms/admin/prvtmail.plb Package body created. SQL> alter system set smtp_out_server='xxx.xxx.xx.xx:25' scope=spfile;restart the database.
Grant execute on utl_mail to user that will be using the utl_mail to send email. Otherwise following error will be thrown
ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'UTL_MAIL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignoredIt's better not to grant execute on utl_mail to public for the same reasons (mainly security) why execute privilege is revoked on utl_smtp,utl_tcp and utl_file (read metalink notes 247093.1 , 234551.1 and 390225.1). "In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions" (Oracle PL/SQL Guide). Test the configuration by sending an email with
exec Utl_Mail.Send( Sender => 'senders email', Recipients => 'recipients email', subject => 'subject line', MESSAGE => 'message' );On 11g some additional steps are required (This was tested with a 11.1.0.7 and 11.2.0.1 standard edition database) Setting up is same as on 10g, run the installation script and set smtp_out_serer parameter. But even after grant execute on utl_mail to user was executed, following error will be thrown when utl_mail is invoked to send an email.
ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 17 ORA-06512: at "SYS.UTL_TCP", line 246 ORA-06512: at "SYS.UTL_SMTP", line 115 ORA-06512: at "SYS.UTL_SMTP", line 138 ORA-06512: at "SYS.UTL_MAIL", line 386 ORA-06512: at "SYS.UTL_MAIL", line 599 ORA-06512: at line 1Granting execute on utl_tcp and utl_smtp is not going to solve this, moreover execute privileges has no impact on utl_mail, all that is needed is for user to have execute on utl_mail.
Create a ACL with user who is going to invoke utl_mail as the principle and granting connect privilege
begin DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( Acl => 'utlmailpkg.xml', Description => 'Normal Access', Principal => 'ASANGA', Is_Grant => True,Privilege => 'connect', Start_Date => Null, End_Date => Null); End; /Add privileges to resolve hosts
begin DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'utlmailpkg.xml', principal => 'ASANGA', is_grant => true, privilege => 'resolve'); end; /Assign the created ACL to the mail server IP and port
begin dbms_network_acl_admin.assign_acl ( acl => 'utlmailpkg.xml', host => '192.168.0.10', lower_port => 25, upper_port => NULL); end; /At the end of executing above PL/SQL code run a commit; without it changes are not visible to users and access denied error will be thrown.
View the privilges in the ACL with
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('utlmailpkg.xml', 'ASANGA', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('utlmailpkg.xml', 'ASANGA', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
FROM dual;After this users can invoke ult_mail to send emails.
