Check SMTP Server Availability for ORA-29278 or ORA-29279 errors using UTL_SMTP to Send Email. (Doc ID 604763.1)

SYMPTOMS

When trying to send email using UTL_SMTP getting following error :

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

Or potentially 

ORA-29279: SMTP permanent error

followed by a SMTP error code.

CAUSE

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.

SOLUTION

You first have to check whether you are able to contact the email server without involving ORACLE.

For example use the following telnet procedure to see whether the mail server is reachable from the Database server: 
******PERFORM THE FOLLOWING OPERATIONS FROM YOUR DATABASE SERVER MACHINE AND NOT FROM OTHER MACHINES.****** 

Note: The information presented here does not apply to TLS/SSL connections . 

a) Start a telnet session to the SMTP server's communication port. (The default port for SMTP is 25)

$telnet <smtp servername as used utl_smtp package> 25


A telnet session should open with a response from smtp:

For eg :

response from smtp ---> 220 ukxxx1 Sendmail SMI-8.6/SMI-SVR4 ready at
Thu, 16 Sep 1999 15:14:25 +0100


b) Now introduce the client machine to the server by typing:
helo domain
(The correct spelling is helo - not hello)


c) Tell the SMTP Gateway who the test email is coming from by typing:
-------> mail from: emailid@domain

For eg :

A response from smtp ---> 250 emailid@domain... Sender ok


d) Tell the SMTP Gateway who to send the test email to by typing:
--------> rcpt to: emailid@domain

For eg :

A response from smtp ---> 250 emailid@domain... Recipient ok

e) Tell the SMTP Gateway what type of information is being sent by typing:
-------> data

A response from smtp ---> 354 Enter mail, end with "." on a line by itself

f) Enter the test message and remember to close the email with a dot "."
Type ---> Subject: SMTP Test
Hello this is an smtp test for EM. .

A response from smtp ---> 250 PAA15913 Message accepted for delivery

g) End the SMTP connection session by typing:
--------> quit

response from smtp ---> 221 ukxxx1 closing connection
The connection has been terminated.

The email should then be delivered to the receiver via the SMTP server.


If the command line test doesn't work, hopefully a helpful error messages from the SMTP server will be displayed indicating a problem will be with the SMTP server setup.

If the above telnet session fails it confirms the network block . You may have to contact your network administrator to remove the block.
Once your network administrator removes the block , retry the above telnet session.
Before using UTL_SMTP , please ensure that the telnet session succeeds.



If the telnet session succeeds, then try the following sample code to test the smtp server : 

Note : Please change the smtp server name in line number 6 and 7 in procedure TESTMAIL.

Note: If the below code fails again with the same error , then use IP Address instead of Mail server name in line number 6 and 7.
 OR
Make the hostname entry in the /etc/hosts file so that it is properly resolved to an IP address

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('<user smtp server name or ip address>',PORT);
UTL_SMTP.HELO(objConnection, '<user smtp server name or ip address>');
UTL_SMTP.MAIL(objConnection, fromm);
UTL_SMTP.RCPT(objConnection, too);
UTL_SMTP.OPEN_DATA(objConnection);

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||'<HTML>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<BODY>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<FONT COLOR="red" FACE="Courier New">'||body||'</FONT>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</BODY>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</HTML>');
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;
/

DECLARE
Vdate Varchar2(25);
BEGIN
Vdate := to_char(sysdate,'dd-mon-yyyy HH:MI:SS AM');
TESTMAIL('xxx.xxx@xxx.com', 'xxx.xxx@xxx.com', 'TESTMAIL','This is a UTL_SMTP-generated email at '|| Vdate,25);
END;
/


Master Note For PL/SQL UTL_SMTP and UTL_MAIL Packages (Doc ID 1137673.1)

APPLIES TO:

PL/SQL - Version 8.1.7.4 to 12.2.0.1 [Release 8.1.7 to 12.2]
Information in this document applies to any platform.
***Checked for relevance on 26-Dec-2018*** 

PURPOSE

Master index for UTL_SMTP / UTL_MAIL.

Provides information on the relevant articles associated with the following topics:

Frequently Asked Questions
How To
Installation and Configuration
Known Bugs
New Features
Sample Code

It's purpose it to point the reader to the most relevant articles and to aid in assist with searching within these areas.

SCOPE

The intended audience is for users of the PL/SQL API and built-in packages.

The notes listed are for novice to experienced users.

DETAILS

Frequently Asked Questions

Note 730746.1 FAQ and Known Issues While Using UTL_SMTP and UTL_MAIL

Note 413099.1 Does UTL_SMTP or UTL_MAIL Support TLS Connection?

Note 369777.1 FAQ about UTL_TCP, UTL_SMTP and UTL_MAIL

How To

Note 106513.1 Basics on How to use UTL_SMTP

Note 269375.1 How to Use the UTL_MAIL Package

Note 74269.1 How to Test an SMTP Mail Gateway From a Command Line

Note 161407.1 How To Send E-mail Containing 8-bit Characters Using UTL_SMTP 

Note 180017.1 How To Specifiy a 'Reply-To' Using UTL_SMTP 

Note 209364.1 How to Send Pager Messages to Mobile Phones using the UTL_SMTP package 

Note 270155.1 How to Programmatic With PL/SQL Send an Email Which Will Allow a Hyperlink to be Created?  

Note 302943.1 Sending Mail Using UTL_SMTP Special Chars And Attach File Problem 

Note 357385.1 How To Send Attachments Of Size Greater Than 32 KB Using UTL_SMTP Package 

Note 468749.1 How to send an Email with Disclaimer? 

Note 553542.1 How to Send Emails with UTL_SMTP containing Unicode Characters in Mail Subject and Body 

Note 745202.1 How to Notify Database Shutdown by Sending a EMAIL ? 

Note 885522.1 How to Send an Email Using UTL_SMTP with Authenticated Mail Server. ? 

Note 946811.1 How to Send Any Type of Attachment with UTL_MAIL 

Note 1323140.1 How to Send an Email Using SMTP over an SSL Connection 

Installation and Configuration

Note 453756.1 Configuring Fine-Grained Access to External Network Services

Note 402678.1 ORA-29279 Relay Errors When Using utl_smtp to Send Email 

Note 604763.1 "ORA-29278: SMTP transient error: 421 Service not available" When Using UTL_SMTP to Send Email.  

Note 754909.1 ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure 

New Features

Note 602986.1 Ora-24248: Unable To Send Email Using UTL_SMTP After Upgrade To 11g

Sample Code
Note 414062.1 Simple Example of Sending Attachments Using UTL_SMTP 

Non-Oracle Issues
There are errors which are not Oracle related. For example, when access to a given mail server is not possible, at times this is an Oracle error especially with 11g and the new security. Other times it can be related to basic network access from the OS machine.
Other errors or issues can actually be related to the mail server itself as described in the following Note.
Note 468323.1  Mail Sent Using UTL_SMTP Not Displaying Correct SENT DATE

Collaborate with and learn from your Peers, Industry Experts and Oracle Support Product Specialists using My Oracle Support Community. Join us here:

Oracle Community - https://communities.oracle.com/

Oracle PL/SQL Community - https://community.oracle.com/community/support/oracle_database/pl_sql

REFERENCES

NOTE:180017.1 - How To Specifiy a 'Reply-To' Using UTL_SMTP
NOTE:209364.1 - How to Send Pager Messages to Mobile Phones Using the UTL_SMTP Package
NOTE:946811.1 - How to Send Any Type of Attachment with UTL_MAIL
NOTE:730746.1 - FAQ and Known Issues While Using UTL_SMTP and UTL_MAIL
NOTE:74269.1 - Enterprise Manager All Releases: How to Test an SMTP Mail Gateway From a Command Line Interface
NOTE:745202.1 - How to Notify Database Shutdown by Sending a EMAIL ?
NOTE:269375.1 - How to Use the UTL_MAIL Package
NOTE:754909.1 - ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure when ACL Assigned to a Role
NOTE:885522.1 - How to Send an Email Using UTL_SMTP with Authenticated Mail Server.
NOTE:161407.1 - How To Send E-mail Containing 8-bit Characters Using UTL_SMTP
NOTE:604763.1 - Check SMTP Server Availability for ORA-29278 or ORA-29279 errors using UTL_SMTP to Send Email.
NOTE:553542.1 - How to Send Emails with UTL_SMTP containing Unicode Characters in Mail Subject and Body
NOTE:602986.1 - Ora-24248: Unable To Send Email Using UTL_SMTP After Upgrade To 11g
NOTE:402678.1 - ORA-29279 Relay Errors When Using UTL_SMTP to Send Email
NOTE:413099.1 - UTL_SMTP Support For SSL/TLS Connection
NOTE:414062.1 - Simple Example of Sending Attachments Using UTL_SMTP
NOTE:106513.1 - Basics on How to use UTL_SMTP
NOTE:1323140.1 - How to Send an Email Using SMTP over an SSL Connection
NOTE:453756.1 - Configuring Fine-Grained Access to External Network Services
NOTE:468323.1 - Mail Sent Using UTL_SMTP Not Displaying Correct SENT DATE
NOTE:270155.1 - How to Programmatically Send an Email Using PL/SQL With a Hyperlink?
NOTE:468749.1 - How to send an Email with Disclaimer?
NOTE:302943.1 - Sending Mail Using UTL_SMTP Special Chars And Attach File Problem
NOTE:369777.1 - FAQ about UTL_TCP, UTL_SMTP and UTL_MAIL
NOTE:357385.1 - How To Send Multiple Attachments Of Size Greater Than 32 KB Using UTL_SMTP Package