Re: email

From: <jdorlon_at_my-deja.com>
Date: Thu, 01 Feb 2001 22:41:43 GMT
Message-ID: <95con5$hq6$1@nnrp1.deja.com>

In article <3a79d3d7.28571874_at_news.atl.bellsouth.net>,   news_at_mreeves.net wrote:
> I need to have several scripts for a report executed daily and emailed
> to several users. What is the easiest way to go about this. Is there a
> way to do this from Oracle Enterprise Manager or to build and schedule
> a database job to do this for me? If not, is there an inexpensive 3rd
> party tool to do this?
>
> Thanks
> mreeves
>

If you have 8i, then you can use the database to send email for you. here is a stored procedure to send mail.

Create or Replace Procedure send_mail
  (mailto varchar2,
   mailfrom varchar2,

   subj     varchar2,
   body     varchar2) as

  c utl_smtp.connection;
  mail_server varchar2(30);
  mail_server_domain varchar2(30);

  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS   BEGIN
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);   END; BEGIN
  mail_server := 'your_mail_server';
  mail_server_domain := 'your_domian';

  c := utl_smtp.open_connection(mail_server);

  utl_smtp.helo(c, mail_server_domain);
  utl_smtp.mail(c, mailfrom);
  utl_smtp.rcpt(c, mailto);
  utl_smtp.open_data(c);
  send_header('From',    '"Sender" <' || mailfrom ||'>');
  send_header('To',      '"Recipient" <' || mailto ||'>');

  send_header('Subject', subj);

  utl_smtp.write_data(c, utl_tcp.CRLF || body);
  utl_smtp.close_data(c);
  utl_smtp.quit(c);

EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN     utl_smtp.quit(c);
    raise_application_error(-20000,
      'Failed to send mail due to the following error: ' || sqlerrm); END; Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 16:41:43 CST