p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle (http://p2p.wrox.com/forumdisplay.php?f=105)
-   -   PL/SQL (http://p2p.wrox.com/showthread.php?t=2276)

cooldude87801 July 31st, 2003 03:45 AM

PL/SQL
 
Hi,
   I am trying to develop a system where it has some data parsed from XML document and stored in Oracle. The system should process the data and send e-mail to the users. I was wondering, since I am new to PL/SQL, how to use stored procedures written in PL/SQL to send e-mails. I would truly appreciate any help or suggestions for this problem.

Thank you

SureshHyd March 3rd, 2005 10:54 AM

check the following code. I have copied it from some other source.

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from varchar2 := 'oracle',
  msg_to varchar2,
  msg_subject varchar2 := 'E-Mail message from your database',
  msg_text varchar2 := '' )
IS
  c utl_tcp.connection;
  rc integer;
BEGIN
  c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'HELO localhost');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);
  rc := utl_tcp.write_line(c, '.'); -- End of message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
  when others then
       raise_application_error(
           -20000, 'Unable to send e-mail message from pl/sql because
            of: '|| sqlerrm);
END;
/
show errors

-- Examples:
set serveroutput on

exec send_mail(msg_to =>'orafaq@orafaq.org');

exec send_mail(msg_to =>'orafaq@orafaq.org', -
           msg_text=>'Look Ma, I can send mail from plsql' - );


All times are GMT -4. The time now is 07:38 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.