Re: Q: UTL_SMTP

From: Charlie Mengler <charliem_at_mwh.com>
Date: Fri, 20 Apr 2001 10:46:55 -0700
Message-ID: <F001.002EE06B.20010420105102@fatcity.com>

HTH & YMMV! REATE OR REPLACE PACKAGE BODY mwh816
AS

   PROCEDURE send_mail (sender IN VARCHAR2,

                        recipient IN VARCHAR2,
                        message   IN VARCHAR2)
   IS
      mailhost                       VARCHAR2(30) := 'chiron.mwh.com';
      mail_conn                      utl_smtp.connection;
      work_string                    VARCHAR2(1023);
      ndx                            INTEGER;
      comma                          CHAR(1) := CHR(44);

   BEGIN
      dbms_output.enable(100000);
      mail_conn := utl_smtp.open_connection(mailhost, 25);
      utl_smtp.helo(mail_conn, mailhost);
      utl_smtp.mail(mail_conn, sender);

      work_string := recipient;
      ndx := instr(work_string,comma)-1;
      while (ndx > 0 )
      LOOP
         dbms_output.put_line(substr(work_string,0,ndx));
         utl_smtp.rcpt(mail_conn, substr(work_string,0,ndx));
         work_string := substr(work_string,ndx+2,length(work_string)-ndx);
         ndx := instr(work_string,comma)-1;
      END LOOP;
      utl_smtp.rcpt(mail_conn, work_string);
      dbms_output.put_line(work_string);
      utl_smtp.data(mail_conn, message);
      utl_smtp.quit(mail_conn);
   EXCEPTION
       WHEN OTHERS THEN
           -- Handle the error
       RAISE;

   END;
END mwh816;

Schoen Volker wrote:
>
> Hi list,
>
> I have a problem with package UTL_SNMP. I have a procedure which sends mails
> to special recipient. My problem is, that the body of the message
> (utl_smtp.data(mail_conn, message);) will not be sent. Any help is welcome.
> Following the procedure.
>
> create or replace PROCEDURE sendmail
> AS
> sERROR_CODE VARCHAR2(10);
> sERROR_TEXT VARCHAR2(250);
> mailhost VARCHAR2(30) := 'mailhost';
> mail_conn utl_smtp.connection;
> sender varchar2(20) :='sender_at_sender.com';
> recipient varchar2(50) :='recipient_at_mailhost.com';
> message varchar2(200) :='This is the message text.';
> BEGIN
> mail_conn := utl_smtp.open_connection(mailhost, 25);
> utl_smtp.helo(mail_conn, mailhost);
> utl_smtp.mail(mail_conn, sender);
> utl_smtp.rcpt(mail_conn, recipient);
> utl_smtp.data(mail_conn, message);
> utl_smtp.quit(mail_conn);
> EXCEPTION
> WHEN OTHERS THEN
> sERROR_CODE := 'ORA' || SQLCODE;
> sERROR_TEXT := sERROR_TEXT || ' ' || SQLERRM;
>
> INSERT INTO ELREFADMIN.MAILERROR
> ( ERROR_CODE, ERROR_TEXT)
> VALUES (sERROR_CODE, sERROR_TEXT);
> END;
> /
>
> TIA
>
> Volker Schön
> E-Mail: mailto:v.schoen_at_inplan.de
> http://www.inplan.de
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Schoen Volker
> INET: v.schoen_at_inplan.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Charlie Mengler               Maintenance Warehouse  
charliem_at_mwh.com              10641 Scripps Summit Ct   
858-831-2229                  San Diego, CA 92131    
The future is here. It is just not evenly distributed.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: charliem_at_mwh.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 20 2001 - 12:46:55 CDT