Re: Stored Procedures and Email

From: Bricklen Anderson <bricklen13_at_hotmail.com>
Date: 15 Feb 2002 10:00:58 -0800
Message-ID: <b416ca2d.0202151000.19e08e81@posting.google.com>

I use a the following code (modified a bit, but should still work):

CREATE OR REPLACE PROCEDURE sendmail (sender VARCHAR2, subject VARCHAR2) IS
 v_connection UTL_SMTP.CONNECTION;

 v_reply         UTL_SMTP.REPLY;
 mesg            VARCHAR2(999);
 crlf            VARCHAR2(2) := CHR(13)||CHR(10);
 err_msg         VARCHAR2(1200);
 mailserver      VARCHAR2(40);
 error_type      VARCHAR2(60);

 email_address VARCHAR2(40);
BEGIN
 FOR n IN (SELECT mailserver,email_address FROM contact_table) LOOP

  v_connection := UTL_SMTP.OPEN_CONNECTION(n.mailserver,25);
  v_reply := UTL_SMTP.HELO(v_connection,n.mailserver);
  v_reply := UTL_SMTP.MAIL(v_connection,n.email_address);
  v_reply := UTL_SMTP.RCPT(v_connection,n.email_address);

  mesg :=

    'Date: '||TO_DATE(sysdate,'DD/MON/RRRR:HH24:MI:SS')||crlf||
    'From: '||sender||crlf||
    'Subject: '||subject||crlf||
    'To: '||n.email_address||crlf;

  v_reply := UTL_SMTP.DATA(v_connection,mesg);   v_reply := UTL_SMTP.QUIT(v_connection);  END LOOP;
EXCEPTION
 WHEN OTHERS THEN
  err_msg := SQLERRM;
  error_type := 'Exception error.';
  INSERT INTO errorlog
(errorlogid,errorlogtype,errorlogname,errorlogtext,errorlogtimestamp)   VALUES (errorlog_errorlogid_seq.nextval,error_type,subject,err_msg,TO_DATE(sysdate,'DD/MON/RRRR:HH24:MI:SS')); COMMIT;
END sendmail;
/
show errors

"Adam Gent" <a.l.gent_at_cs.cf.ac.uk> wrote in message news:<1013787834.30041.0_at_iapetus.uk.clara.net>...
> Hi,
>
> Is there anyway to send emails from within oracle stored procedures.
>
> Thanks,
>
> Adam Gent
> a.l.gent_at_cs.cf.ac.uk
Received on Fri Feb 15 2002 - 12:00:58 CST