send a mail from pl/sql [message #347579] |
Fri, 12 September 2008 04:15  |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi All,
I am using the following ( which i got by googling) procedure
to send the mail ,.It is not throwing any error at the same i am not receive the mail also.Pls can let me know what may be the problem ???
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/
Procedure created.
SQL> show error;
No errors.
SQL> exec send_mail(msg_to=>'Ravi_sastry@satyam.com', -
> msg_subject => 'Hello from Oracle', -
> msg_text => 'This is the body of the message'-
> );
PL/SQL procedure successfully completed.
|
|
|
|
Re: send a mail from pl/sql [message #347584 is a reply to message #347582] |
Fri, 12 September 2008 04:33   |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi Michel,
I am not sure ..From command Prompt i am trying to check in DOS
as follows:
>nslookup
Default Server: bsdadc001.corp.satyam.ad
Address: 172.19.57.200
pls let me know .
|
|
|
|
Re: send a mail from pl/sql [message #347590 is a reply to message #347587] |
Fri, 12 September 2008 04:55   |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi Frank ,
I am getting the following error :
SQL> exec send_mail(msg_to=>'panyamravi@yahoo.co.in', -
> msg_subject => 'Hello from Oracle', -
> msg_text => 'This is the body of the message'-
> );
BEGIN send_mail(msg_to=>'panyamravi@yahoo.co.in', msg_subject => 'Hello from Oracle', msg_text =
*
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 "SYSTEM.SEND_MAIL", line 14
ORA-06512: at line 1
I treid as suggested in http://www.dba-oracle.com/sf_ora_29278_smtp_transient_error_string_bc1.htm
But still getting the same error.
|
|
|
|
Re: send a mail from pl/sql [message #347598 is a reply to message #347592] |
Fri, 12 September 2008 05:07   |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi Michel,
I have the DB in My local System only.
How ever i am getting the follwoing error when trying to telnet
>telnet bsd-pc2999.corp.satyam.ad:25
Connecting To bsd-pc2999.corp.satyam.ad:25...Could not open connection to the ho
st, on port 23: Connect failed
|
|
|
Re: send a mail from pl/sql [message #347601 is a reply to message #347598] |
Fri, 12 September 2008 05:28  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This means you have no mailserver, so you cannot use localhost as mailhost.
The follow-up is not Oracle related, so you'll have to search the web how to install or connect to a mailserver
|
|
|