Home » Infrastructure » Unix » SMTP service (oracle,9i,SunOs)
SMTP service [message #295466] Tue, 22 January 2008 06:57 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi.

I can able to send mail from the unix using mailx command but when I use the following snippet in sql*plus tool, it is showing network error.

declare
c utl_tcp.connection;
a exception;
pragma exception_init(a,-29260);
begin
c := utl_tcp.open_connection('127.0.0.1', 25);
dbms_output.put_line('Opened Successfully');
utl_tcp.close_connection(c);
exception
when a then
dbms_output.put_line('Network Error');
when others then
dbms_output.put_line('Other Error'||chr(10)||sqlerrm);
end;

Please let me know whether some service has to run in UNIX to use utl_tcp package?

Thanks.
Re: SMTP service [message #295473 is a reply to message #295466] Tue, 22 January 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 9i, use utl_smpt.
Search on AskTom there are several threads on this subject there.

Regards
Michel
Re: SMTP service [message #295479 is a reply to message #295473] Tue, 22 January 2008 07:38 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks Michel for your reply.

My production box is running with the same configuration like oracle 9i,SunOs but I can use this package with out any problem!

Thanks.
Re: SMTP service [message #295481 is a reply to message #295479] Tue, 22 January 2008 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't post the error, how are we supposed to know it?

Regards
Michel
Re: SMTP service [message #295489 is a reply to message #295481] Tue, 22 January 2008 08:09 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi Michel,

Please have a look at the source and error.


SQL> declare
2 c utl_tcp.connection;
3 begin
4 c := utl_tcp.open_connection('localhost', 25);
5 dbms_output.put_line('Opened Successfully');
6 utl_tcp.close_connection(c);
7 end;
8 .
SQL> /
declare
*
ERROR at line 1:
ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.UTL_TCP", line 410
ORA-06512: at "SYS.UTL_TCP", line 252
ORA-06512: at line 4

Re: SMTP service [message #295492 is a reply to message #295489] Tue, 22 January 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With almost 200 posts you should know how to post code.
OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

Regards
Michel
Re: SMTP service [message #295494 is a reply to message #295492] Tue, 22 January 2008 08:22 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Sorry Michel,

Please have a look at the following

declare
c utl_tcp.connection;
begin
c := utl_tcp.open_connection('localhost', 25);
dbms_output.put_line('Opened Successfully');
utl_tcp.close_connection(c);
end;
/
declare
*
ERROR at line 1:
ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.UTL_TCP", line 410
ORA-06512: at "SYS.UTL_TCP", line 252
ORA-06512: at line 4
Re: SMTP service [message #295500 is a reply to message #295494] Tue, 22 January 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see the difference with the previous one.
Do you read the link?
Do you use the preview button?

Regards
Michel
Re: SMTP service [message #295511 is a reply to message #295500] Tue, 22 January 2008 08:45 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Please have a look

declare
        c  utl_tcp.connection;
begin
	c := utl_tcp.open_connection('localhost', 25); 
	dbms_output.put_line('Opened Successfully');
	utl_tcp.close_connection(c);
end;
/
declare
	*
	ERROR at line 1:
	ORA-29260: network error: TNS:no listener
	ORA-06512: at "SYS.UTL_TCP", line 410
	ORA-06512: at "SYS.UTL_TCP", line 252
	ORA-06512: at line 4
Re: SMTP service [message #295514 is a reply to message #295511] Tue, 22 January 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have someone listening at port 25 in your local host?

Regards
Michel
Re: SMTP service [message #295515 is a reply to message #295514] Tue, 22 January 2008 08:50 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Michel,

Please let me know how to check this information?

Thanks.
Re: SMTP service [message #295516 is a reply to message #295515] Tue, 22 January 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you a smtp server/daemon (most often smtpd on Unix), for instance?

Regards
Michel
Re: SMTP service [message #295519 is a reply to message #295516] Tue, 22 January 2008 09:09 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
I don't find any smtpd daemon in my servers... Should be some name for the SMTP daemon...

Thanks.
Re: SMTP service [message #295530 is a reply to message #295519] Tue, 22 January 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask your sysadmin for this.

Regards
Michel
Re: SMTP service [message #295533 is a reply to message #295515] Tue, 22 January 2008 10:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
shahnazurs wrote on Tue, 22 January 2008 15:50
Michel,

Please let me know how to check this information?

Thanks.

Try to open a telnet session to port 25 of the db-server
If you run into trouble with stuff like this, start by peeling of as many layers of complication as possible.
In this case, test it without the database layer in between.
Re: SMTP service [message #295613 is a reply to message #295533] Tue, 22 January 2008 22:14 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
No idea why did you move this thread to different group without answering my query.
As I said earlier, I can send mail using mailx command but not with utl_tcp package.

You suggested some possibilities but nothing works. Is there any expert there to answer my thread?

Thanks.
Re: SMTP service [message #295621 is a reply to message #295466] Tue, 22 January 2008 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post the results from the following command:
netstat -nl
Re: SMTP service [message #295639 is a reply to message #295621] Tue, 22 January 2008 23:34 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
netstat -nl
netstat: illegal option -- l
usage: netstat [-anv] [-f address_family]
       netstat [-g | -p | -s] [-n] [-f address_family] [-P protocol]
       netstat -m
       netstat -i [-I interface] [-an] [-f address_family] [interval]
       netstat -r [-anv] [-f address_family]
       netstat -M [-ns] [-f address_family]
       netstat -D [-I interface] [-f address_family]

Re: SMTP service [message #295647 is a reply to message #295613] Wed, 23 January 2008 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No idea why did you move this thread to different group without answering my query.

How can you know who moves the thread if he did not say he move it? What does make you say he didn't answer?

use "netstat -nv"

Regards
Michel
Re: SMTP service [message #295649 is a reply to message #295613] Wed, 23 January 2008 00:27 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
shahnazurs wrote on Wed, 23 January 2008 05:14
No idea why did you move this thread to different group without answering my query.
As I said earlier, I can send mail using mailx command but not with utl_tcp package.

I moved your post from the pl/sql experts to here, because this does not involve expert pl/sql knowledge.
Re: SMTP service [message #295650 is a reply to message #295647] Wed, 23 January 2008 00:28 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Please have a look at the statistics by using
netstat -nv

TCP: IPv4
Local/Remote Address Swind  Snext     Suna   Rwind  Rnext     Rack    Rto   Mss  State
-------------------- ----- -------- -------- ----- -------- -------- ----- ----- -----
161.121.31.13.23
192.161.49.137.4950  64075 00000000 00000000 24660 00000000 00000000  1113  1370 ESTABLISHED
161.121.31.13.23
192.161.49.137.1474  63300 00000002 00000000 24660 00000000 00000000  1111  1370 ESTABLISHED
161.121.31.13.1526
192.161.49.137.1628  64512 00000000 00000000 24660 00000000 00000000   986  1370 ESTABLISHED
161.121.31.13.1526
192.161.49.137.2176  63403 00000000 00000000 24660 00000000 00000000  1066  1370 ESTABLISHED
161.121.31.13.1526
192.161.49.135.4068  63150 00000000 00000000 24660 00000000 00000000  1098  1370 ESTABLISHED
161.121.31.13.1526
192.161.49.140.2309  63959 00000000 00000000 24660 00000000 00000000  1059  1370 ESTABLISHED

Active UNIX domain sockets
Address  Type          Vnode     Conn  Local Addr      Remote Addr
30004ab1b08 stream-ord 30004c5a2d0 00000000 /var/tmp/.oracle/s#468.1
30004ab1cb8 stream-ord 300046f0958 00000000 /var/run/prngd-pool

Re: SMTP service [message #295653 is a reply to message #295613] Wed, 23 January 2008 00:32 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
shahnazurs wrote on Wed, 23 January 2008 05:14
You suggested some possibilities but nothing works. Is there any expert there to answer my thread?


If a telnet to port 25 does not work, you have no smtp daemon on that box listening to port 25. This is not a database or pl/sql issue. I don't know what MTA mailx uses, but apparently not an SMTP-service.
First configure your box, and when you can send mail through a telnet session, only then move a step further and try to do it from the database.
Re: SMTP service [message #295663 is a reply to message #295653] Wed, 23 January 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know what MTA mailx uses, but apparently not an SMTP-service.

Or it is configured to use a SMTP service on another box.

@shahnazurs
Your netstat output clearly shows that noone is listening on port 25.

I move the thread to Unix forum which is more its place.

Regards
Michel
Re: SMTP service [message #295668 is a reply to message #295653] Wed, 23 January 2008 01:04 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks all for your suggestions.

I will be checking with my system admin regarding which service is being used by MTA mailx command.

Michel,
You said again about the telnet 25, if I can able to send mail using mailx command then this ensure mail server is configured. Please correct me if I am wrong.

Thanks.
Re: SMTP service [message #295670 is a reply to message #295668] Wed, 23 January 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can able to send mail using mailx command then this ensure mail server is configured

Yes but it does not mean that the service resides in THIS box.

Regards
Michel
Re: SMTP service [message #295679 is a reply to message #295670] Wed, 23 January 2008 01:31 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Is it possible to check whether sendmail daemon uses which box/server's SMTP services?

Thanks.
Re: SMTP service [message #295733 is a reply to message #295679] Wed, 23 January 2008 03:56 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes. Send an e-mail to the network/mail administrator asking him :

Which SMTP server should I use for my project?


Since he is probably the only one who can tell you definitely. He also might have different SMTP servers running in a backup/failover scenario and would want you to use a specific DNS name alias or something in your code.
Previous Topic: To Execute the script from another server
Next Topic: Date formatting in unix shell script
Goto Forum:
  


Current Time: Thu Mar 28 05:52:03 CDT 2024