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.

Report message to a moderator

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.

Report message to a moderator

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: 68641
Registered: March 2007
Location: Nanterre, France, http://...
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

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

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)!

Report message to a moderator

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.

Report message to a moderator

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?

Report message to a moderator

Previous Topic: How Procedure Message can display in Unix Log file
Next Topic: process one file at a time
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 03:08:12 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.