Home » SQL & PL/SQL » SQL & PL/SQL » MAIL SENDING THROUGH PL/SQL
MAIL SENDING THROUGH PL/SQL [message #186479] Tue, 08 August 2006 02:20 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi Gurus,

I am able to send the mail through Outlook Exporess but
not able to send a mail though procedure.
Facing an error as under :-

SQL> CREATE OR REPLACE PROCEDURE SEND_MAIL (
  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('smtp.mail.yahoo.com', 25);
 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.

SQL> exec send_mail('shilpisoft.com','anilsapra9@gmail.com');
BEGIN send_mail('shilpisoft.com','anilsapra9@gmail.com'); END;

*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260: network error: 
TNS:protocol adapter error 
ORA-06512: at "SYSADM.SEND_MAIL", line 30 
ORA-06512: at line 1 



Pl. help.

With regards

Anil
Re: MAIL SENDING THROUGH PL/SQL [message #186486 is a reply to message #186479] Tue, 08 August 2006 02:38 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

I tried executing the procedure as under :-
SQL> exec send_mail('shilpisoft@yahoo.co.in','anilsapra9@gmail.com');
BEGIN send_mail('shilpisoft@yahoo.co.in','anilsapra9@gmail.com'); END;

*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260: network error:
TNS:protocol adapter error
ORA-06512: at "SYSADM.SEND_MAIL", line 30
ORA-06512: at line 1


Pl.help.

Anil
Re: MAIL SENDING THROUGH PL/SQL [message #186570 is a reply to message #186486] Tue, 08 August 2006 09:19 Go to previous messageGo to next message
ruyue
Messages: 9
Registered: July 2006
Junior Member
I tried the procedure,it completed correctly.
The error, "TNS:protocol adapter error ",perhaps is caused by
your database configuration.Check your tnsnames.ora, if the host name,port,sid is right .The listener is started or not.You can
check by tnsping command.

Re: MAIL SENDING THROUGH PL/SQL [message #186684 is a reply to message #186479] Wed, 09 August 2006 01:27 Go to previous message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Thanks for your reply.

I have checked in my outlook mail configuration :-


===========================================================
  OUTGOING MAIL SMTP      : smtp.mail.yahoo.com
  OUTGOING MAIL SMTP PORT : 25

& in TNSNAMES.ORA  is as under :-
======================
PRAK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 131.108.2.144)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRAK)
    )  )
=====================================================
What should I define in below line connections :-

c := utl_tcp.open_connection('smtp.mail.yahoo.com', 25);

I tried using ('131.108.2.144', 1521')
then sqlplus goes in NOT Responding mode;

======================================================


Pl. Help how to define these settings...

With Regards,

Anil


Previous Topic: percentage calculations
Next Topic: Create Unique Constraint using Execute immediate
Goto Forum:
  


Current Time: Fri Dec 09 23:34:34 CST 2016

Total time taken to generate the page: 0.07639 seconds