topic Re: Notification mail failure in Oracle in Operating System - HP-UX

Notification mail failure in Oracle

sryella — Mon, 27 Feb 2006 00:18:35 GMT

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.

Re: Notification mail failure in Oracle

Arunvijai_4 — Mon, 27 Feb 2006 00:24:46 GMT

Hi Srinivas,

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

-Arun

Re: Notification mail failure in Oracle

Indira Aramandla — Mon, 27 Feb 2006 00:38:52 GMT

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

Re: Notification mail failure in Oracle

Yogeeraj_1 — Mon, 27 Feb 2006 00:52:45 GMT

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

Re: Notification mail failure in Oracle

Yogeeraj_1 — Mon, 27 Feb 2006 01:03:04 GMT

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

Re: Notification mail failure in Oracle

sryella — Mon, 27 Feb 2006 01:52:33 GMT

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.

Re: Notification mail failure in Oracle

sryella — Mon, 27 Feb 2006 01:59:59 GMT

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.

Re: Notification mail failure in Oracle

Yogeeraj_1 — Mon, 27 Feb 2006 04:59:41 GMT

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

Re: Notification mail failure in Oracle

Steven Chen_1 — Tue, 28 Feb 2006 16:54:42 GMT

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

Re: Notification mail failure in Oracle

sryella — Mon, 06 Mar 2006 21:32:01 GMT

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.

Re: Notification mail failure in Oracle

sryella — Mon, 06 Mar 2006 21:33:17 GMT

Modified UTL_SMTP_SEND_MAIL with the correct smtp host information.

Re: Notification mail failure in Oracle

keshavkovela — Mon, 08 Jan 2007 02:40:03 GMT

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