Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2
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.
Suppose that you created the following wrapper procedure in PL/SQL:
CREATE OR REPLACE PROCEDURE UTILS.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; /
To get this procedure working on Oracle 11g, there are several steps you need to take.
First, you need to actually 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 Apr 27 14:49:33 2012 SQL> connect / as sysdba Connected. SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb SQL> grant execute on utl_mail to public;
Next, 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 a “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;
Finally, you need to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL. Without an ACL, you will receive the following error: “ORA-24247: network access denied by access control list (ACL)“.
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; BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'mail_access.xml', host => 'mymailserver@mydomain.com', lower_port => 25, upper_port => 25 ); COMMIT; END;
After these steps, you should be able to successfully send e-mails from within the database:
begin
utils.send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘matthiash@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
*Action:
anonymous block completed
Matthias
hi im using oracle xe 10.2… utl_mail throws an error this utl_mail procedure not found..
Hello,
I don’t have 10g XE anymore, but I believe that UTL_MAIL is not included with this version.
A possible workaround can be found here:
http://www.dynasource.gr/index.php/el/blog-el/item/34-oracle_10g_xe_mail
Hope this helps,
Matthias
Cool
Super, Simple and to the point! Loved it and it worked for me.
Samir
I am getting following error : ORA-29279: SMTP permanent error: 530 authentication required.
Kindly give me solution ASAP.
Thanks in advance….
This is most likely a problem with the configuration of your mail server. Please contact your system administrator for a solution.
Matthias
Hi, I am using oracle 11g, what shall I do if utilmail.sql is unable to open while installing utl_mail package
kindly give me solution
Thanks in advance….
I don’t understand your question… The file is not there? Maybe you used the wrong name? I’s UTLMAIL.sql, not UTILMAIL.sql.
Matthias
Thanks this works perfectly.
Thanks Great Post.
Hi, does this now work in 11203, if the smtp server needs authentification? In earlier releases it did not work as far as I know. Juergen
Hello Jürgen,
I never tried it, but I believe you need UTL_SMTP.AUTH for this and not UTL_MAIL… See http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm#BHAGEECF
Matthias