Home » Infrastructure » Unix » Error in send_mail (Oracle,9i,SunOs)
Error in send_mail [message #309030] Wed, 26 March 2008 04:48 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

When I try to execute the following anonymous code

begin
	send_mail_local('asl@gmail.com','bas@yahoo.com.com',
	'hi','how are you doing');
end;

getting the following error

ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260: network error: TNS:no listener
ORA-06512: at "ABC.SEND_MAIL_LOCAL", line 36
ORA-06512: at line 2


I am able to execute the same block in test environment and it executes successfully and then it sent mail.

Thanks.
Re: Error in send_mail [message #309035 is a reply to message #309030] Wed, 26 March 2008 04:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
please provide code for send_mail_local.
Re: Error in send_mail [message #309036 is a reply to message #309030] Wed, 26 March 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-29260: network error: %s
 *Cause:  A network error occurred.
 *Action: Fix the network error and retry the operation.

Regards
Michel
Re: Error in send_mail [message #309041 is a reply to message #309036] Wed, 26 March 2008 05:05 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

Please find the snippet below

CREATE OR REPLACE PROCEDURE SEND_MAIL_LOCAL (
  msg_from    varchar2 := USER,
  msg_to      varchar2,
  msg_subject varchar2 := 'E-Mail message ',
  msg_text    varchar2 := '' )
IS
  c  utl_tcp.connection;
  rc integer;
  rcc varchar2(4000);
BEGIN
  c := utl_tcp.open_connection('localhost', 25);       -- open the SMTP port 25 on local machine
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rcc :=utl_tcp.get_line(c, TRUE);
  rc := utl_tcp.write_line(c, 'HELLO localhost');
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rcc :=utl_tcp.get_line(c, TRUE);
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
  rcc :=utl_tcp.get_line(c, TRUE);
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA');                 -- Start message body
  rcc :=utl_tcp.get_line(c, TRUE);
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);
  rc := utl_tcp.write_line(c, '.');                    -- End of message body
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');
  rcc :=utl_tcp.get_line(c, TRUE);
  --vstat.cout(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c);                         -- Close the connection
EXCEPTION
  when others then
       raise_application_error(
           -20000, 'Unable to send e-mail message from pl/sql because of: '||
           sqlerrm);
       vstat.cout('Unable to send e-mail message from pl/sql because of: '|| sqlerrm);
END;
/


/michel, I have seen the description of this error ready. Oracle s/w is installed on UNIX box, I can able to send mail using mailx command from the UNIX box. Why it is showing error then? what does that no listener error too in that?

Thanks.
Re: Error in send_mail [message #309121 is a reply to message #309030] Wed, 26 March 2008 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>c := utl_tcp.open_connection('localhost', 25);
change "localhost" to actual hostname or IP# & try again

post output from

netstat -nl

[Updated on: Wed, 26 March 2008 08:33] by Moderator

Report message to a moderator

Re: Error in send_mail [message #309279 is a reply to message #309121] Thu, 27 March 2008 00:01 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

Thanks for your comments.
I tried replacing localhost by IP address but still got the same error.

Please find below is the netstat o/p

shadev:[/export/home/sha]netstat -n

TCP: IPv4
   Local Address        Remote Address    Swind Send-Q Rwind Recv-Q  State
-------------------- -------------------- ----- ------ ----- ------ -------
162.125.33.12.38878 162.125.33.12.1521  32768      0 32768      0 ESTABLISHED
162.125.33.12.1521  162.125.33.12.38878 32768      0 32768      0 ESTABLISHED
162.125.33.12.1521  192.168.49.16.2045   64112      0 24660      0 ESTABLISHED
162.125.33.12.39555 162.54.52.224.1984   48391      0 24820      0 TIME_WAIT
162.125.33.12.23    192.168.49.16.2084   64081      1 24660      0 ESTABLISHED

Active UNIX domain sockets
Address  Type          Vnode     Conn  Local Addr      Remote Addr
300024a15f8 stream-ord 30002486d18 00000000 /tmp/smc898/cmdsock
300024a0368 stream-ord 3000282ada8 00000000 /var/tmp/.oracle/s#2469.1
300024a0d88 stream-ord 300087a2bb8 00000000 /var/tmp/.oracle/s#15098.1
300024a1cb8 stream-ord 30002487e98 00000000 /var/run/prngd-pool


Thanks.
Re: Error in send_mail [message #309290 is a reply to message #309030] Thu, 27 March 2008 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am not surprised.

I requested
>netstat -nl
you decided that
>netstat -n

was close enough

You're On Your Own (YOYO)!
Re: Error in send_mail [message #309292 is a reply to message #309290] Thu, 27 March 2008 01:09 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

I mentioned ready my OS is SunOS, so l option is not working for netstat command, let me know what can I do?

Thanks.
Re: Error in send_mail [message #309452 is a reply to message #309030] Thu, 27 March 2008 08:42 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Sorry, but I don't have access to a Solaris box to do
man netstat
to learn which switch shows which ports are being actively listened upon.

What happened when you made suggested code change?
Previous Topic: How Procedure Message can display in Unix Log file
Next Topic: process one file at a time
Goto Forum:
  


Current Time: Thu Dec 05 18:57:13 CST 2024