Notification mail failure in Oracle

[sryella]

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.

[Arunvijai_4]

Hi Srinivas,

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

-Arun

[Indira Aramandla]

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

[Yogeeraj_1]

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

[Yogeeraj_1 · Accepted solution]

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

[sryella]

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]

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]

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

[Steven Chen_1]

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

[sryella]

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.