Enable Oracle database to send emails via SMTP server
Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.
1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP
Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.
2. Check SMTP access of database Linux server on mail server
– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:
$ telnet smtp_server 25
If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:
helo mail from: my_email@my_company.com # you should see "Sender OK' rcpt to: my_email@my_company.com # you should see "Recipient OK" data # Start mail input test email via SMTP and orcl DB [Enter] . # mail should be sent [Enter] quit
3. Apply for mail SMTP access
Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example:
Please allow SMTP traffic at smtpgate.mycompany.com for the following new server:
Hostname: ..com
IP: 99.999.99.99
Function: Oracle production DB …
Outgoing email: my_app@my_company.com
Amount emails per month: around 50-100
After that your mail admins will add a sender IP address to a corporate SMTP mail server to allow the sender Linux server access the SMTP server. This will enable the SMTP server sending emails on behalf of the sender (your Oracle database Linux box).
Note that the mail server can check if the sender email exists. If not, you can send an email from a dummy email account.
4. Sending email from Oracle database via SMTP
-- Place twice in the below procedure your actual smtp_server name create or replace procedure testmail (fromm varchar2,too varchar2,sub varchar2,body varchar2,port number) is objConnection utl_smtp.connection; vrData varchar2(32000); BEGIN objConnection := UTL_smtp.open_connection('<smtp_server>',port); UTL_smtp.helo(objConnection, '<smtp_server>'); UTL_smtp.mail(objConnection, fromm); UTL_smtp.rcpt(objConnection, too); UTL_smtp.open_data(objConnection); /* ** Sending the header information */ UTL_smtp.write_data(objConnection, 'From: '||fromm || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'To: '||too || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/html;'); UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<span style="color: red; font-family: Courier New;">'||body||'</span>'); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.close_data(objConnection); UTL_smtp.quit(objConnection); EXCEPTION WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN UTL_smtp.quit(objConnection); dbms_output.put_line(sqlerrm); WHEN OTHERS THEN UTL_smtp.quit(objConnection); dbms_output.put_line(sqlerrm); END testmail; / -- this should send an email from a dummy account to your email address execute testmail ('dummy@my_company.com','reciever_email@my_company.com','test Subject','Test Body Text',25);
– In case of following ORA- error go to step 2 and 3:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 21
ORA-06512: at “SYS.UTL_SMTP”, line 97
ORA-06512: at “SYS.UTL_SMTP”, line 139
The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem
is not with the UTL_SMTP package but the fact that your database server’s network configuration does
not allow it to contact an external SMTP server.
– In case a following Oracle error, go to next step and enable ACL in Oracle 11g database:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 54
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_SMTP”, line 699
ORA-06512: at “me.TESTMAIL”, line 35
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
5. Enable access control list (ACL) for your network hosts on 11g+
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.
Create an ACL with one database user in Oracle database
BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'smtp-gate-permissions.xml', description => 'Permissions for smtp gate', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect' ); COMMIT; END; /
Assign an ACL to one or more network hosts
BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'smtp-gate-permissions.xml', host => '<smtp_server>', lower_port => 25, upper_port => null); 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 /
So that was my action plan for providing your applications an ability to send email via SMTP protocol from an Oracle database Linux server using a real example with UTL_SMTP package including ORA- error handling and ACL creation on Oracle 11g.
Hi,
it was very helpful, thanks!
thanks!
I do all the last steps but also return the same error
ORA-29278: SMTP transient error: 421 Service not available
The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem is not with the UTL_SMTP package but the fact that your database server’s network configuration does not allow it to contact an external server.
So you have to go through all the steps I mentioned in my post to make the thing work: from enabling SMTP access to enabling ACL.
— Kirill
I tred the above code , and received email too with no error, but issue is that there was no content, no subject no body. We have oracle 11g.
do we need to enable any other setting for write_data to perform well.
Hi Ruchika
Usually nothing more is required.
In the above procedure you have to replace twice your actual smtp_server name and execute it as described.
I’ve just tested it. It works fine on 11gR2
— Kirill
This post helped me in troubleshooting the SMTP error. Thanks!
Hello,
I have 2 linux server with database. I am able to send mail, from both server. but in one of the server its take 10sec. to send one mail, while in other its not taking even 1sec. Can you guide me.
Hi Bharat
I would check first if there is such a big time difference in sending email from Linux side.
If yes, it can be somewhere at OS, Network, DNS resolution side.
If not, can be a DB related issue. In this case try to trace your PL/SQL procedure (10046 SQL trace) and see where the time spent.
— Kirill
What should I do, to configure my Oracle 10g Database to connect to SMTP server. I’m able to ping the SMTP server. and I’ve permissions to relay mails from that server. Can you help me to send an email from Oracle server. I’m getting 29278 error.
Thanks.
Hi Srinivas
Procedure should be the same. Ensure first you can send email from OS to your corporate email address.
— Kirill
Hi, I am getting ora-24247.
have created ACL.
how to confirm the ACL created is correct?
Can some one help please.
Hi Kishor
I’ve updated my post with SQL to confirm the created Oracle ACL is correct
Please see above.
— Kirill
Thanks Kirill for your article.
On an Oracle 10g Instance it works fine without the need to run the scripts that you have above.
On Oracle 11g Express the send_mail does not work and is giving me the error that you are showing above.
I run the scripts that you have given us using the SYS user on principal userDemo but still is giving me the same error. Do I need to restart the instance?
The Oracle 11g express is installed on a Windows Server 2008 R2 Standard.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘smtp-gate-permissions.xml’,
description => ‘Permissions for smtp gate’,
principal => ‘userdemo’,
is_grant => TRUE,
privilege => ‘connect’
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘smtp-gate-permissions.xml’,
host => ‘mail-out.cytanet.com.cy’,
lower_port => 25,
upper_port => null);
COMMIT;
END;
/
Hi Christos
I do not have experience with Oracle XE but I believe that you have to set ACL in Oracle 11g XE also.
Ensure you go throw all the steps I described above properly. Additionally double check 2 more things:
– XML DB is there and setup properly
– try to play with following parameter:
smtp_out_server
Good luck and let me know how you will fix the issue.
— Kirill
Hi Kirill,
I have solved the issue. It seems that if you use an SMTP account that does not have login credentials is causing this problem. Another problem might be that I was using an SMTP account with PORT 25.
After I tried another SMTP account that required authentication and on PORT 80 it worked.
Regards,
Chris
Hi Kirill ,
Let me thank you first of all for sharing above knowledge.
I am trying to achieve mailing functionality by creating a package in oracle using pl/sql. And I ensure that my entire pl/sql script is inclined to the example shared by you in above procedure for sending mails from oracle db.
But I am facing below error while executing package:
ORA-30678: too many open connections
ORA-06512: at “SYS.UTL_TCP”, line 28
ORA-06512: at “SYS.UTL_TCP”, line 257
ORA-06512: at “SYS.UTL_SMTP”, line 115
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SENDMAILS”, line 24
ORA-06512: at line 1
Note: I have also explicitly closed the connection i used in my script.
Please advise how to proceed further in this scenario and achieve the mailing target from my script.
Thanks,
Krishna
krishnakantshukla86@gmail.com
Hi Kirill ,
I addition to the above,
I also tried checking for number of open connections , i guess i don’t have sufficient access in solaris box to check the same.
I believe maximum no. of open connections allowed is 20.
Thanks,
Krishna
Hi Krishna
FYI: the utl_smtp package has a hard limit of 16 connections per session and you can’t change it. Please double check the code I provided. It definitely should work.
You might hit an Oracle Bug 8262564 on Oracle versions below 11.2. In this case, if the exceptions in you PL/SQL code are raised for any reason, the connections can be left open. So the connection is getting closed only in those cases when UTL_MAIL.SEND succeeds without any error (again due to this Bug). When the number of such open connections exceed 16, it is failing with error ORA-30678: too many open connections. Please note this is fixed in the 11.2.0.1 or higher Oracle Database.
Hope it helps
— Kirill
hi
thanks for your useful post
i have created acl but also return hateful errror:ORA-29278: SMTP transient error: 421 Service not available
this my telnet output
$ telnet smtp.gmail.com 25
Trying 173.194.65.108…
Connected to smtp.gmail.com (173.194.65.108).
Escape character is ‘^]’.
220 mx.google.com ESMTP h13sm5483081wiw.4 – gsmtp
helo
250 mx.google.com at your service
mail from:soltani69azadeh@gmail.com
530 5.7.0 Must issue a STARTTLS command
first.h13sm5483081wiw.4 – gsmtp
starttls
220 2.0.0 Ready to start TLS
mail from:soltnai69azadeh@gmail.com
Connection closed by foreign host.
what is wrong?
what should i do?
thanks
thank you very much, the ACL part solved my problem (which I straggled alot to find a solution for it)
ORA-29279: SMTP permanent error: 530 5.7.1 Authentication required. What may be problem?
Hi Arun
Oracle error ORA-29279 potentially indicates that the Server on which the database resides needs to be registered with the SMTP server.
So please go throuth steps 2 and 3 in my article to check that.
— Kirill
Hi Kirill,
i gone through your article,am facing a problem like
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. r1sm5219268igp.20 – gsmtp.
am using oracle 11.2.0.1, please advise how to proceed further
Thanks
Vijay
Hi kirill,
I created ACL and assing ACL to the host…
still i am facing the problem..
Plz help…
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 54
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_SMTP”, line 197
ORA-06512: at “CORPORATE.SEND_MAIL_CC”, line 24
ORA-06512: at line 7
Regards,
sudhakar.
Hi Kirill,
i tried lots of times to send mail but getting:
535 5.7.3 Authentication unsuccessful
error .
My SMTP server requires TLS and authentication…
when i telnet to server on port:587
after ehlo command, it shows GSSAPI and NTLM methods only. like this:
….
250-AUTH GSSAPI NTLM
….
how can i authenticate by using NTLM or GSSAPI ?
thanks in advance.
We have Oracle 11g on Linux server with an application. Sending email works fine from the database using the stmp server, however, users are not getting email notification even though the log file in the database show email has been sent.
What will be the obvious reason for this.
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 21
ORA-06512: at “SYS.UTL_SMTP”, line 97
ORA-06512: at “SYS.UTL_SMTP”, line 139
Using in oracle 9i
Please advise how to proceed further in this scenario and achieve the mailing target from my script.
Thanks,
Zahir Ahmad
zahir3007@hotmail.com
upgrade to 12c first 🙂
— Kirill
Spam filter 🙂
— Kirill
Try Oracle doc: http://docs.oracle.com/database/121/DBSEG/asossl.htm#DBSEG070
— Kirill
We have a custom PL/SQL API for sending email.
When it is invoked from Anonymous Block it works fine.
But when we try to invoke it through- another custom PL/SQL pkg it gives error like :
“—– PL/SQL Call Stack —–
object line object
handle number name
0x99f9e488 1 anonymous block
0xc0c623b0 256 package body APPS.XX_ACH_PYMT_BOFA_PKG
0xb2e0cd18 1 anonymous block
0xbc3da0f8 711 package body APPS.WF_EVENT
0xbc3da0f8 2141 package body APPS.WF_EVENT
0xbc3da0f8 3021 package body APPS.WF_EVENT
0xbc3da0f8 1187 package body APPS.WF_EVENT
0xc0cb7370 110 package body APPS.FND_CONCURRENT_BUSINESS_EVENT
0xc0cb7370 240 package body APPS.FND_CONCURRENT_BUSINESS_EVENT
0xbe857988 1 anonymous block
”
“ORA-24247: network access denied by access control list (ACL)
”
“ORA-06512: at “SYS.UTL_TCP”, line 19
ORA-06512: at “SYS.UTL_TCP”, line 280
ORA-06512: at “SYS.UTL_SMTP”, line 163
ORA-06512: at “SYS.UTL_SMTP”, line 199
ORA-06512: at “APPS.XX_COMMON_UTIL_PKG”, line 972
ORA-06512: at “APPS.XX_ACH_PYMT_BOFA_PKG”, line 236
“
Hi,
eg:-abc view is there.this abc view everyday 1AM automatic refresh that time some data added that i want that information send to mail. if it is posible.can you share a script
hi Kirill I am getting this error please help me on this I am using my local pc and loaded oracle 11g with windows operating system.
please help me on configuring smtp on my mail server.
thanks in advance
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 29
ORA-06512: at “SYS.UTL_SMTP”, line 108
ORA-06512: at “SYS.UTL_SMTP”, line 463
ORA-06512: at “SCOTT.TESTMAIL”, line 28
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 1
29278. 00000 – “SMTP transient error: %s”
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.
The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem
is not with the UTL_SMTP package but the fact that your database server’s network configuration does
not allow it to contact an external server.
Is there any way to log all emails send via utl_smtp?