Sending e-mails from within the Oracle database using the
UTL_MAIL PL/SQL package used to be quite easy in Oracle 10g. However, in
Oracle 11gR2, things have changed.To enable SMTP package to send mail we
need to perform following steps.
1.
First, you need to actually install the UTL_MAIL package.
It’s not installed by default on 11g
2.
Next, you need to add the address and port of the e-mail
server to the “smtp_out_server” initialization parameter.
3.
Next to create an Access Control List (ACL) for your
e-mail server and grant the necessary users access to this ACL.
4.
Create the wrapper procedure in PL/SQL.
Let’s see above steps in detail.
STEP1: Install
the UTL_MAIL package. It’s not installed by default on 11g
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 6 10:21:07 2015
Copyright (c) 1982, 2011, Oracle.
All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/utlmail.sql
Package created.
Synonym created.
SQL> @?/rdbms/admin/prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.
SQL> grant execute on utl_mail to public;
Grant succeeded.
STEP2: add the
address and port of the e-mail server to the “smtp_out_server”
initialization parameter.
You need to add the address and port of the e-mail server
to the “smtp_out_server” initialization parameter. If you do not do this, you
will receive “ORA-06502: PL/SQL: numeric or value error” error when you try to
use the UTL_MAIL package.
Execute the following with
user SYS as SYSDBA:
SQL> alter system set smtp_out_server
= 'mymailserver@mydomain.com:25' scope=both;
3. Next to
create an Access Control List (ACL) for your e-mail server and grant the
necessary user’s access to this ACL.
The network utility family of PL/SQL packages, such as
UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to
make network callouts from the database using raw TCP or using higher-level
protocols built on raw TCP. As of Oracle 11g a new package
DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access
using access control lists (ACL) implemented by XML DB. Below I show major
steps that are required to enable ACL for user SCOTT. You have to run them as
SYS or SYSDBA user. Read Oracle documentation for more details.
Without an ACL, you will receive the following error:
ORA-24247: network access denied by access control list (ACL)
Create an ACL
with one database user in Oracle database
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
(
acl
=> 'mail_access.xml',
description
=> 'Permissions to access e-mail server.',
principal
=> 'PUBLIC',
is_grant
=> TRUE,
privilege
=> 'connect');
COMMIT;
END;
Assign an
ACL to one or more network hosts
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL
(
acl
=> 'mail_access.xml',
host
=> 'mymailserver@mydomain.com',
lower_port
=> 25,
upper_port
=> 25
);
COMMIT;
END;
Confirm the
created ACL is correct
SELECT host, lower_port, upper_port,
acl
FROM dba_network_acls
/
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY
HH24:MI') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS
end_date
FROM
dba_network_acl_privileges
/
4. Create the wrapper
procedure in PL/SQL
CREATE OR REPLACE PROCEDURE SEND_MAIL (
p_sender
IN VARCHAR2,
p_recipients
IN VARCHAR2,
p_cc
IN VARCHAR2 DEFAULT NULL,
p_bcc
IN VARCHAR2 DEFAULT NULL,
p_subject
IN VARCHAR2,
p_message
IN VARCHAR2,
p_mime_type
IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii'
)
IS
BEGIN
UTL_MAIL.SEND
(sender => p_sender,
recipients
=> p_recipients,
cc
=> p_cc,
bcc
=> p_bcc,
subject
=> p_subject,
message
=> p_message,
mime_type
=> p_mime_type
);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END send_mail;
/
5.
After these steps, you should be able to successfully send e-mails from within
the database:
Begin
send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘Amit@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘Amit@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
*Action:
anonymous block completed
I hope this article helped you. Your suggestions/feedback are most
welcome.
Keep learning... Have a great day!!!
Keep learning... Have a great day!!!
