Home » RDBMS Server » Server Administration » unable to send email through oracledb (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0)
| unable to send email through oracledb [message #364826] |
Wed, 10 December 2008 03:28  |
panduoracledba
Messages: 14
Registered: September 2008
|
Junior Member |
|
|
Hi..everyone.
i am using windows XP sp2 &
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0.
SRIRAM123> conn / as sysdba
Connected.
SRIRAM123> /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
5 rows selected.
SRIRAM123> ed
Wrote file afiedt.buf
1 select object_name
2 from dba_objects
3 where object_name like upper('utl_mail')
4* and object_type like 'PACKAGE'
SRIRAM123> /
OBJECT_NAME
--------------------------------------------------
UTL_MAIL
1 row selected.
SRIRAM123> @d:\sriramoracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql
Package created.
Synonym created.
SRIRAM123> @d:\sriramoracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb
Package body created.
No errors.
SRIRAM123> alter system set smtp_out_server='ip_address' scope=both;
alter system set smtp_out_server='ip_address' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SRIRAM123> ed
Wrote file afiedt.buf
1* alter system set smtp_out_server='ip_address' scope=spfile
SRIRAM123> /
System altered.
SRIRAM123> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SRIRAM123> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SRIRAM123> BEGIN
2 UTL_MAIL.send(sender => 'sriram',
3 recipients => 'e-mail'[/email],
4 subject => 'UTL_MAIL Test',
5 message => 'If you get this message it worked!');
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at "SYS.UTL_MAIL", line 228
ORA-06512: at "SYS.UTL_MAIL", line 350
ORA-06512: at line 2
Here i dnt know the smpt port number.....i am using the default one(25).
Am i doing anything wrong with these procdure..please help me..
and onee more ....
SRIRAM123> CREATE OR REPLACE PROCEDURE mailout
2 (
3 sender IN VARCHAR2,
4 recipient IN VARCHAR2,
5 ccrecipient IN VARCHAR2,
6 subject IN VARCHAR2,
7 message IN VARCHAR2
8 ) IS
9 crlf VARCHAR2(2):= UTL_TCP.CRLF;
10 connection utl_smtp.connection;
11 mailhost VARCHAR2(30) := 'ip_address';
12 header VARCHAR2(1000);
13 BEGIN
14 --
15 -- Start the connection.
16 --
17 connection := utl_smtp.open_connection(mailhost,25);
18 header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
19 'From: '||sender||''||crlf||
20 'Subject: '||subject||crlf||
21 'To: '||recipient||crlf||
22 'CC: '||ccrecipient;
23 --
24 -- Handshake with the SMTP server
25 --
26 utl_smtp.helo(connection, mailhost);
27 utl_smtp.mail(connection, sender);
28 utl_smtp.rcpt(connection, recipient);
29 utl_smtp.rcpt(connection, ccrecipient);
30 utl_smtp.open_data(connection);
31 --
32 -- Write the header
33 --
34 utl_smtp.write_data(connection, header);
35 --
36 -- The crlf is required to distinguish that what comes next is not simply part of the header..
37 --
38 utl_smtp.write_data(connection, crlf ||message);
39 utl_smtp.close_data(connection);
40 utl_smtp.quit(connection);
41 EXCEPTION
42 WHEN UTL_SMTP.INVALID_OPERATION THEN
43 dbms_output.put_line(' Invalid Operation in SMTP transaction.');
44 WHEN UTL_SMTP.TRANSIENT_ERROR THEN
45 dbms_output.put_line(' Temporary problems with sending email - try again later.');
46 WHEN UTL_SMTP.PERMANENT_ERROR THEN
47 dbms_output.put_line(' Errors in code for SMTP transaction.');
48 END;
49 /
Procedure created.
SRIRAM123> execute mailout('sriram','e-mail'[/email],'e-mail'[/email],'Hi...','testmail');
PL/SQL procedure successfully completed.
SRIRAM123>
but there is no such mail in the inbox.what i have to do to rectify the problem? and what exactly the problem is?
thanks & regards
[EDITED by LF as per OP's request]
[Updated on: Thu, 26 November 2009 06:38] by Moderator Report message to a moderator |
|
|
|
| Re: unable to send email through oracledb [message #364834 is a reply to message #364826] |
Wed, 10 December 2008 03:51  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| Quote: |
ORA-29278: SMTP transient error: 421 Service not available
|
There is no mail server running on the server and port you are trying to use.
This problem has basically nothing to do with Oracle, and can not be solved from within Oracle. You have to contact the admin of the mail server.
For testing, I suggest you install a normal mail program somewhere, and once you are able to successfully send mails from that normal mail program via SMTP, then use the same settings from within Oracle.
Also, read the forum guidelines on how to format your post.
|
|
|
|
| Re: unable to send email through oracledb [message #364852 is a reply to message #364834] |
Wed, 10 December 2008 04:55 |
panduoracledba
Messages: 14
Registered: September 2008
|
Junior Member |
|
|
thanks ThomasG
Quote:I suggest you install a normal mail program somewhere, and once you are able to successfully send mails from that normal mail program via SMTP, then use the same settings from within Oracle. sorry for my ignornce......As a fresher ,
i dont know anything about SMTP(how to check which port it`s using).normally with my system i can able to send/receive mails to my mail id with in the network and above.the problem is only when ever i am trying to send the mail through oracle.please have a look at my second procedure....
SRIRAM123> CREATE OR REPLACE PROCEDURE mailout
2 (
3 sender IN VARCHAR2,
4 recipient IN VARCHAR2,
5 ccrecipient IN VARCHAR2,
6 subject IN VARCHAR2,
7 message IN VARCHAR2
8 ) IS
9 crlf VARCHAR2(2):= UTL_TCP.CRLF;
10 connection utl_smtp.connection;
11 mailhost VARCHAR2(30) := 'ip_address';
12 header VARCHAR2(1000);
13 BEGIN
14 --
15 -- Start the connection.
16 --
17 connection := utl_smtp.open_connection(mailhost,25);
18 header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
19 'From: '||sender||''||crlf||
20 'Subject: '||subject||crlf||
21 'To: '||recipient||crlf||
22 'CC: '||ccrecipient;
23 --
24 -- Handshake with the SMTP server
25 --
26 utl_smtp.helo(connection, mailhost);
27 utl_smtp.mail(connection, sender);
28 utl_smtp.rcpt(connection, recipient);
29 utl_smtp.rcpt(connection, ccrecipient);
30 utl_smtp.open_data(connection);
31 --
32 -- Write the header
33 --
34 utl_smtp.write_data(connection, header);
35 --
36 -- The crlf is required to distinguish that what comes next is not simply part of the header..
37 --
38 utl_smtp.write_data(connection, crlf ||message);
39 utl_smtp.close_data(connection);
40 utl_smtp.quit(connection);
41 EXCEPTION
42 WHEN UTL_SMTP.INVALID_OPERATION THEN
43 dbms_output.put_line(' Invalid Operation in SMTP transaction.');
44 WHEN UTL_SMTP.TRANSIENT_ERROR THEN
45 dbms_output.put_line(' Temporary problems with sending email - try again later.');
46 WHEN UTL_SMTP.PERMANENT_ERROR THEN
47 dbms_output.put_line(' Errors in code for SMTP transaction.');
48 END;
49 /
Procedure created.
SRIRAM123> execute mailout('sriram','xxx@yahoo.com','xxx@gmail.com','Hi...','testmail');
PL/SQL procedure successfully completed.
SRIRAM123>
but there is no such mail in the inbox.what i have to do to rectify the problem? and what exactly the problem is?
it is displaying
PL/SQL procedure successfully completed
if any error in my post please let me know about that.
if i have to write any program for making such changes in smtp settings...please let me know HOW & where?
thanks & regards
[EDITED by LF as per OP's request]
[Updated on: Thu, 26 November 2009 06:39] by Moderator Report message to a moderator |
|
|
|
| Re: unable to send email through oracledb [message #364856 is a reply to message #364826] |
Wed, 10 December 2008 05:12 |
panduoracledba
Messages: 14
Registered: September 2008
|
Junior Member |
|
|
SRIRAM123> set serveroutput on size 100000
SRIRAM123> CREATE OR REPLACE PROCEDURE SEND_MAIL_sriram123 (
2 msg_from varchar2 := 'oracle',
3 msg_to varchar2,
4 msg_subject varchar2 := 'E-Mail message from your database',
5 msg_text varchar2 := '' )
6 IS
7 c utl_tcp.connection;
8 rc integer;
9 BEGIN
10 c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
11 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
12 rc := utl_tcp.write_line(c, 'HELO localhost');
13 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
14 rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
15 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
16 rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
17 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
18 rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
19 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
20 rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
21 rc := utl_tcp.write_line(c, '');
22 rc := utl_tcp.write_line(c, msg_text);
23 rc := utl_tcp.write_line(c, '.'); -- End of message body
24 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
25 rc := utl_tcp.write_line(c, 'QUIT');
26 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
27 utl_tcp.close_connection(c); -- Close the connection
28 EXCEPTION
29 when others then
30 raise_application_error(
31 -20000, 'Unable to send e-mail message from pl/sql because of: '||
32 sqlerrm);
33 END;
34 /
Procedure created.
SRIRAM123> execute SEND_MAIL_sriram123(msg_to =>'xxx@yyy.com');
BEGIN SEND_MAIL_sriram123(msg_to =>'xxx@yyy.com'); END;
*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.SEND_MAIL_SRIRAM123", line 30
ORA-06512: at line 1
whats the error?
thanks®ards
[EDITED by LF as per OP's request]
[Updated on: Thu, 26 November 2009 06:40] by Moderator Report message to a moderator |
|
|
|
| Re: unable to send email through oracledb [message #364859 is a reply to message #364852] |
Wed, 10 December 2008 05:18 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Thanks for formatting the post.
In this second post, it looks like Oracle has delivered the mail successfully to the mail server.
Although it COULD be that DBMS_OUTPUT is not displayed in the SQLPlus session. Maybe add an additional
dbms_output.put_line(' Mail send successfully.');
right before the exception block to check if that is displayed.
If that gets displayed, then the mail should have been delivered to the mail server. Then you or the admin of the mail server would have to take a look at the logs of the mail server to see what the mail server is doing with it.
Also: you can check if an SMPT server is running on a server and port with an
from the command prompt for example.
You should be greeted by the banner of the SMTP server, and then you can use "quit" to exit again.
[EDITED as per OP's request]
[Updated on: Thu, 26 November 2009 06:41] by Moderator Report message to a moderator |
|
|
|
| Re: unable to send email through oracledb [message #364886 is a reply to message #364826] |
Wed, 10 December 2008 06:11 |
panduoracledba
Messages: 14
Registered: September 2008
|
Junior Member |
|
|
thanks ThomasG
here
SRIRAM123> set serveroutput on size 10000
SRIRAM123> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PROCEDURE mailout
2 (
3 sender IN VARCHAR2,
4 recipient IN VARCHAR2,
5 ccrecipient IN VARCHAR2,
6 subject IN VARCHAR2,
7 message IN VARCHAR2
8 ) IS
9 crlf VARCHAR2(2):= UTL_TCP.CRLF;
10 connection utl_smtp.connection;
11 mailhost VARCHAR2(30) := 'xxx.yyy.com';
12 header VARCHAR2(1000);
13 BEGIN
14 --
15 -- Start the connection.
16 --
17 connection := utl_smtp.open_connection(mailhost,25);
18 header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
19 'From: '||sender||''||crlf||
20 'Subject: '||subject||crlf||
21 'To: '||recipient||crlf||
22 'CC: '||ccrecipient;
23 --
24 -- Handshake with the SMTP server
25 --
26 utl_smtp.helo(connection, mailhost);
27 utl_smtp.mail(connection, sender);
28 utl_smtp.rcpt(connection, recipient);
29 utl_smtp.rcpt(connection, ccrecipient);
30 utl_smtp.open_data(connection);
31 --
32 -- Write the header
33 --
34 utl_smtp.write_data(connection, header);
35 --
36 -- The crlf is required to distinguish that what comes next is not simply part of the header..
37 --
38 utl_smtp.write_data(connection, crlf ||message);
39 utl_smtp.close_data(connection);
40 utl_smtp.quit(connection);
41 dbms_output.put_line(' Mail send successfully.');
42 EXCEPTION
43 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error or UTL_SMTP.INVALID_OPERATION THEN
44 BEGIN
45 utl_smtp.quit(connection);
46 EXCEPTION
47 WHEN utl_smtp.transient_error
48 OR utl_smtp.permanent_error or UTL_SMTP.INVALID_OPERATION THEN
49 NULL;
50 END;
51 raise_application_error(-20000, SQLERRM);
52* END;
SRIRAM123> /
Procedure created.
SRIRAM123> execute mailout('sriram','sriram','sriram','sriram','sriram');
BEGIN mailout('sriram','sriram','sriram','sriram','sriram'); END;
*
ERROR at line 1:
ORA-20000: ORA-29278: SMTP transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.MAILOUT", line 51
ORA-06512: at line 1
SRIRAM123> execute mailout('sriram','sriram','xxx@yyy.com','sriram','sriram');
BEGIN mailout('sriram','sriram','xxx@yyy.com','sriram','sriram'); END;
*
ERROR at line 1:
ORA-20000: ORA-29278: SMTP transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.MAILOUT", line 51
ORA-06512: at line 1
SRIRAM123>
here are the telnet stats.....
C:\Documents and Settings\administrator.1xxx>netstat
Active Connections
Proto Local Address Foreign Address State
<removed by LF>
C:\Documents and Settings\xxx.yyy>NETSTAT -ANO
Active Connections
Proto Local Address Foreign Address State PID
<removed by LF>
C:\Documents and Settings\xxx.yyy>telnet ip_address 25
Connecting To ip_address...Could not open connection to the host, on port 25: Connect failed
C:\Documents and Settings\administrator.1xxx>
and also please see this ..please excuse me if thisis wrong way of submission.here i am reposting the form
SRIRAM123> set serveroutput on size 100000
SRIRAM123> CREATE OR REPLACE PROCEDURE SEND_MAIL_sriram123 (
2 msg_from varchar2 := 'oracle',
3 msg_to varchar2,
4 msg_subject varchar2 := 'E-Mail message from your database',
5 msg_text varchar2 := '' )
6 IS
7 c utl_tcp.connection;
8 rc integer;
9 BEGIN
10 c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
11 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
12 rc := utl_tcp.write_line(c, 'HELO localhost');
13 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
14 rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
15 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
16 rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
17 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
18 rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
19 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
20 rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
21 rc := utl_tcp.write_line(c, '');
22 rc := utl_tcp.write_line(c, msg_text);
23 rc := utl_tcp.write_line(c, '.'); -- End of message body
24 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
25 rc := utl_tcp.write_line(c, 'QUIT');
26 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
27 utl_tcp.close_connection(c); -- Close the connection
28 EXCEPTION
29 when others then
30 raise_application_error(
31 -20000, 'Unable to send e-mail message from pl/sql because of: '||
32 sqlerrm);
33 END;
34 /
Procedure created.
SRIRAM123> execute SEND_MAIL_sriram123(msg_to =>'xxx@yyy.com');
BEGIN SEND_MAIL_sriram123(msg_to =>'xxx@yyy.com'); END;
*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.SEND_MAIL_SRIRAM123", line 30
ORA-06512: at line 1
C:\Documents and Settings\administrator.1xxx>telnet ip_address 25
Connecting To ip_address...Could not open connection to the host, on port 25: Connect failed
C:\Documents and Settings\administrator.1xxx>lsnrctl
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 10-DEC-2008 17:46:31
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.yyy.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date 03-DEC-2008 10:25:26
Uptime 7 days 7 hr. 21 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\sriramoracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File D:\sriramoracle\product\10.1.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.yyy.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.yyy.com)(PORT=8080))(Presentation=HTTP)(Se
ssion=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.yyy.com)(PORT=2100))(Presentation=FTP)(Ses
sion=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "sriram" has 1 instance(s).
Instance "sriram123", status READY, has 1 handler(s) for this service...
Service "sriram123XDB" has 1 instance(s).
Instance "sriram123", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
please suggest me with required ...
thanks in advance
sriram
[EDITED by LF as per OP's request]
[Updated on: Fri, 27 November 2009 02:35] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: unable to send email through oracledb [message #364912 is a reply to message #364902] |
Wed, 10 December 2008 07:55 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:
Connecting To ip_address ...Could not open connection to the host, on port 25: Connect failed
So I suggest the same thing I have already suggested three or four times:
There is no SMTP server running on the server you want to use. There is NOTHING that can be done on the Oracle side, get in touch with your network / mail administrators and have them set it up correctly or give you the correct server/port etc.. information.
As for the TNS error: Get rid of the "when others" in your code to see where the error really comes from.
[EDITED by LF as per OP's request]
[Updated on: Thu, 26 November 2009 06:45] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: unable to send email through oracledb [message #376130 is a reply to message #364912] |
Tue, 16 December 2008 03:47 |
panduoracledba
Messages: 14
Registered: September 2008
|
Junior Member |
|
|
Hi...ThomasG.good afternoon.here with i am sending you the output of your suggestion. Due to my sysadmin is not available ...i am sending the output to you please let me know the port number....
C:\telnet ip_address 25
220*********************************************************************************************************************
thats the out i got.so is 220 my default port num?
thanks & regards
sriram
[EDITED by LF as per OP's request]
[Updated on: Thu, 26 November 2009 06:45] by Moderator Report message to a moderator |
|
|
|
| Re: unable to send email through oracledb [message #376135 is a reply to message #376130] |
Tue, 16 December 2008 04:27 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I can't tell you anything.
I have the same chance of knowing how your sysadmin has set up the mail system than I would have knowing which colour his underwear is today. You HAVE to ask HIM.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jul 05 06:19:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
