General
cancel
Showing results for 
Search instead for 
Did you mean: 

Notification mail failure in Oracle

SOLVED
Go to solution
sryella
Occasional Advisor

Notification mail failure in Oracle

Hi,

When I try to send notification mail from Oracle using the below SQL procedure, I am getting the error "ORA-29278: SMTP transient error: 421 Service not available".


DECLARE
BEGIN
send_mail(NULL,'test',null,'/home/srini/test.dat',null,'sr_yella@yahoo.com.sr_yella@yahoo.com','sr_yella@yahoo.com','sr_yella@yahoo.com');
--SEND_MAIL ( A_CALL_NM_STR, A_SUBJECT_STR, A_FILE_TYPE_CHAR, A_FILE_NM_STR, A_MAIL_STDIN_STR, A_EM_ADDR_STR, A_CC_NAMES, A_BCC_NAMES );
COMMIT;
END;


ORA-20001: ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "ECLIPSE.SEND_MAIL", line 40
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 21


I have configured sendmail in Unix and able to send emails using sendmail at OS level.
Can anybody let me know what could be the possible reason?

Thanks in Advance.

Regards,

Srinivas.
11 REPLIES
Arunvijai_4
Honored Contributor

Re: Notification mail failure in Oracle

Hi Srinivas,

Check this url, http://forums.oracle.com/forums/message.jspa?messageID=630550
http://forums.oracle.com/forums/thread.jspa?messageID=1177838

-Arun
"A ship in the harbor is safe, but that is not what ships are built for"
Indira Aramandla
Honored Contributor

Re: Notification mail failure in Oracle

Hi Srinivas,

ORA-29278: SMTP transient error: 421 Service not available. The cause for this would be that the mailhost cannot be contacted.

Check the mailhost specified in UTL_SMTP.OPEN_CONNECTION and UTL_SMTP.HELO.



IA


Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Notification mail failure in Oracle

hi,

the error means, that your host you are using is not running smtp services.

ask your SA's where there is an smtp server in your network that does relaying.

Also make sure that the address defined in your variable "l_mailhost" is running smtp on port 25.


hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor
Solution

Re: Notification mail failure in Oracle

hi again Srinivas,

one simple example that i usually use is:
create or replace
PROCEDURE send_mail (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
-- Note that you have to use a host
-- that supports SMTP and that you have access to.
-- You do not have access to this host and must change it
l_mailhost VARCHAR2(255) := 'mysvr.co.mu';
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn, p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
/

set serveroutput on
declare
l_start number := dbms_utility.get_time;
begin
send_mail( 'yd@mysvr.com',
'helloworld@gmail.com', 'Hello world' );
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds' );
end;
/

Please make appropriate changes..

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
sryella
Occasional Advisor

Re: Notification mail failure in Oracle

Hi,

May be a simple query, but I am new to pl/sql procedures.

Can you please let me know how I can check the value assigned to mailhost or l_mailhost in SEND_MAIL procedure?
And how can I change that value?

Thanks & Regards,

Srinivas.
sryella
Occasional Advisor

Re: Notification mail failure in Oracle

By the way, I have created a procedure with name SEND_SMTP_MAIL with the code that Yogeeraj has given me with correct smtp relay host and I was able to send the email successfully.. Thanks to Yogeeraj.
Now,I just want to modify mailhost/l_mailhost in SEND_MAIL procedure.
Please let me know how I can do that.

Thanks & Regards,

Srinivas.
Yogeeraj_1
Honored Contributor

Re: Notification mail failure in Oracle

hi srinivas,

you will need to open the SEND_MAIL and make the change.

one simple editor for your procedure which you can use is Oracle SQL Developer (formerly Project Raptor). Please download it from http://www.oracle.com/technology/products/database/project_raptor/index.html

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven Chen_1
Super Advisor

Re: Notification mail failure in Oracle

Oracle sendmail requires Java jvm. Make sure you have jvm working properly. If it is broken on some point, reload it.

Reloading should include initjvm.sql & initplsj.sql.

Then exec ultsmtp.sql and grant right to users.

Then check your sendmail procedure that I feel the syntax is not right. Check the sendmail prototype with syntax, which should be like "send_mail(â registry@abcdefg.comâ ,â en88@yahoo.comâ ,â testâ ,â test1â );

HTH.

Steven



Steve
sryella
Occasional Advisor

Re: Notification mail failure in Oracle

Hi,

I downloaded sql developer and modified smtp host in UTL_SMTP_SEND_MAIL procedure.
Now I am able to send the mials.
Thanks a lot for your help.

Thanks & Regards,

Srinivas.
sryella
Occasional Advisor

Re: Notification mail failure in Oracle

Modified UTL_SMTP_SEND_MAIL with the correct smtp host information.
keshavkovela
Occasional Visitor

Re: Notification mail failure in Oracle

Hi Friends,

I need a solution for the below requirement .


I need to send report output as URL link to particular
employees e-mail_id , when the employee clicks the
URL, the report output would open.
Please send the solution as soon as possible if u have
expereinced ever.

R3ply me at keshavkovela@gmail.com

Thanks,
Keshav