UTL_MAIL PL/SQL package provides a much simplified interface to send email using Oracle database. Both on 10g and 11g "
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" (Oracle PL/SQL Guide).
This is true on 10g and 11g. To install
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
scripts should be run as sys and
smtp_out_server
parameter 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 1
There 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 ignored
It'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 1
Granting 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.
