ORA-29278: SMTP transient error: string

I got ” ORA-29278: SMTP transient error: string ”  error in Oracle database.

 

ORA-29278: SMTP transient error: string

 

Details of error are as follows.

ORA-29278: SMTP transient error: string

Cause: A SMTP transient error occurred.

Action: Correct the error and retry the SMTP operation.

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
   

 

 

SMTP transient error: string

This ORA-29278 errors are related with the SMTP transient error occurred.

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.

 

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: [email protected]

For eg :

A response from smtp —> 250 [email protected]… Sender ok

d) Tell the SMTP Gateway who to send the test email to by typing:
——–> rcpt to: [email protected]

For eg :

A response from smtp —> 250 [email protected]… 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('[email protected]', '[email protected]', 'TESTMAIL','This is a UTL_SMTP-generated email at '|| Vdate,25);
END;
/

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 564 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply