Home » SQL & PL/SQL » SQL & PL/SQL » send a mail from pl/sql
send a mail from pl/sql [message #347579] Fri, 12 September 2008 04:15 Go to next message
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 #347582 is a reply to message #347579] Fri, 12 September 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have you a smtp server?

Regards
Michel
Re: send a mail from pl/sql [message #347584 is a reply to message #347582] Fri, 12 September 2008 04:33 Go to previous messageGo to next message
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 #347587 is a reply to message #347584] Fri, 12 September 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Start by removing the exceptions.
dbms_output.put_line is NOT the way you want to handle errors.
Re: send a mail from pl/sql [message #347590 is a reply to message #347587] Fri, 12 September 2008 04:55 Go to previous messageGo to next message
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 #347592 is a reply to message #347590] Fri, 12 September 2008 04:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To repeat Michel's question:
Do you have a mailserver running?
Can you do a telnet to port 25 of your db-server?
Re: send a mail from pl/sql [message #347598 is a reply to message #347592] Fri, 12 September 2008 05:07 Go to previous messageGo to next message
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 Go to previous message
Frank
Messages: 7880
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
Previous Topic: Forall statement
Next Topic: exiting plsql block
Goto Forum:
  


Current Time: Fri Dec 02 22:54:30 CST 2016

Total time taken to generate the page: 0.12186 seconds