Enable Oracle database to send emails via SMTP server

July 21, 2013 by 36 Comments 

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.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

36 Responses to “Enable Oracle database to send emails via SMTP server”
  1. Robert says:

    Hi,

    it was very helpful, thanks!

  2. Karema Mohamed says:

    I do all the last steps but also return the same error
    ORA-29278: SMTP transient error: 421 Service not available

  3. Kirill Loifman says:

    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

  4. Ruchika says:

    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.

  5. Kirill Loifman says:

    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

  6. Arief says:

    This post helped me in troubleshooting the SMTP error. Thanks!

  7. Bharat says:

    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.

  8. Kirill Loifman says:

    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

  9. Srinivas Chunduri says:

    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.

  10. Kirill Loifman says:

    Hi Srinivas
    Procedure should be the same. Ensure first you can send email from OS to your corporate email address.
    — Kirill

  11. Kishor says:

    Hi, I am getting ora-24247.
    have created ACL.
    how to confirm the ACL created is correct?

    Can some one help please.

  12. Kirill Loifman says:

    Hi Kishor
    I’ve updated my post with SQL to confirm the created Oracle ACL is correct
    Please see above.
    — Kirill

  13. Christos says:

    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;
    /

  14. Kirill Loifman says:

    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

  15. Christos says:

    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

  16. krishna says:

    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

  17. krishna says:

    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

  18. Kirill Loifman says:

    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

  19. azadeh says:

    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

  20. hameedkb says:

    thank you very much, the ACL part solved my problem (which I straggled alot to find a solution for it)

  21. Arun says:

    ORA-29279: SMTP permanent error: 530 5.7.1 Authentication required. What may be problem?

  22. Kirill Loifman says:

    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

  23. vijay.B.G says:

    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

  24. sudhakar says:

    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.

  25. ozymandias says:

    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.

  26. Dee says:

    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.

  27. Zahir says:

    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

  28. Kirill Loifman says:

    upgrade to 12c first 🙂
    — Kirill

  29. Kirill Loifman says:

    Spam filter 🙂
    — Kirill

  30. Amol says:

    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

  31. nagarjuna says:

    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

  32. lispy leon says:

    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.

  33. Kirill Loifman says:

    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.

  34. emin says:

    Is there any way to log all emails send via utl_smtp?

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email