RE: UTL_SMTP - Multiple Recipients

  • From: "Goulet, Dick" <DGoulet@xxxxxxxx>
  • To: <Nagarajan.Subbiah@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 5 Dec 2005 13:01:11 -0500

Here is the procedure that we're using, multiple addressee's is not a
problem.  Feel free to copy anything you like:

create or replace procedure vicor_mail(addressee in varchar2, subject in
varchar2,
                                       message in varchar2, sender in
varchar2 default null) is
  c utl_smtp.connection;
  v_crlf VARCHAR2(2):= CHR(13)||CHR(10);
  v_msg varchar2(4000);
  v_add_src varchar2(2000);
  v_addr varchar2(40);
  msg_chunck varchar2(2000);
  mail_server varchar2(30);
  madr varchar2(100);
  slen number := 1;
  dmy number := 0;
  a number := 0;
  t number := 0;
  logfp utl_file.file_type;

begin
  logfp :=  utl_file.fopen(orafunc.utl_home, 'vicor_mail.log','a');
  utl_file.putf(logfp, '%s %s %s %s\n', to_char(sysdate,'DD-MON-YYYY
HH24:MI'),addressee, subject, message);
  select default_mail_server into mail_server
  from oracle_server;
  c := utl_smtp.open_connection(mail_server);
  utl_smtp.helo(c, mail_server);
  if(sender is not null) then
    madr := replace(sender,' ','_');
    utl_smtp.mail(c, madr);
  else utl_smtp.mail(c, 'Import<import@xxxxxxxx>');
  end if;
  v_add_src := replace(addressee,' ','_');
  if(instr(addressee,',') = 0) then utl_smtp.rcpt(c, v_add_src);
  else
    v_add_src := replace(addressee,' ','_')||',';
    while(instr(v_add_src,',',slen) > 0) loop
      v_addr := substr(v_add_src, slen, instr(substr(v_add_src,
slen),',')-1);
      slen := slen+instr(substr(v_add_src, slen),',');
      utl_smtp.rcpt(c, v_addr);
    end loop;
  end if;
  utl_smtp.open_data(c);
  utl_smtp.write_data(c, 'SUBJECT: '||subject||utl_tcp.CRLF);
  v_msg := utl_tcp.CRLF||replace(replace(message,'  ',' '),'
',chr(10));
  utl_file.put_line(logfp, v_msg);
  utl_file.put_line(logfp, length(v_msg));
  if(length(v_msg) > 2000) then
     while(a < length(v_msg)) loop
       msg_chunck := substr(v_msg, a, 2000);
       a := a+length(msg_chunck);
       utl_smtp.write_data(c,msg_chunck);
     end loop;
  else  utl_smtp.write_data(c, v_msg);
  end if;
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
  utl_file.fclose(logfp);
end;
/ 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Subbiah, Nagarajan
Sent: Monday, December 05, 2005 11:37 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: UTL_SMTP - Multiple Recipients

Hi,

I am using the below package for the send mail functionality from
Oracle.
When I use pass the multiple e-mail addresses (using comma or semicolon
delimeter), I get the following error message. Do I have to parse and
split
the e-mail variable and call this package multiple times OR is there a
way
to use the multiple e-mail addresses in the UTL_SMTP?

DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: 
ORA-29279: SMTP permanent error: 553 malformed address:
Nsubbiah@xxxxxxxxx,ess.admin@xxxxxxxx
ORA-06512: at line 10

Thanks in Advance.
Raja

CREATE OR REPLACE 
package body webutils
as

PROCEDURE send_email(
                p_mailhost              IN VARCHAR2,
                p_recipient     IN VARCHAR2,
                p_message_body  IN VARCHAR2,
                p_sender                IN VARCHAR2,
                p_subject               IN VARCHAR2,
                p_cc                    IN VARCHAR2 DEFAULT NULL,
                p_mailport              IN NUMBER DEFAULT 25
                )
/*
This procedure sends an SMTP mail via the nominated mailhost using
UTL_SMTP.
Parameters are fairly self explanatory.

Restrictions:                   Messages are limited to 32k including
Header.
                                                NO attachments.

Requires:                               Oracle JVM
                                                Oracle 8.1.7
                                                At least one valid
recipient.

Version History:
                        V1.0 NL 08/11/01 -- webutils
                        V1.1 NL 27/11/03 -- generalized
*/
IS
        l_mail_conn             UTL_SMTP.connection;
        crlf                            CHAR(2) := CHR(13)||CHR(10);
        l_message                       VARCHAR2(32767);
BEGIN
/* Create message header per RFC */
        l_message := 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss')
||crlf;
        l_message := l_message||'From: '||p_sender||crlf;
        l_message := l_message||'To: '||p_recipient||crlf;
        l_message := l_message||'CC: '||p_cc||crlf;
        l_message := l_message||'Subject: '||p_subject||crlf;
/* add body after single cr/lf */
        l_message := l_message||crlf||p_message_body;
/* done */
        l_mail_conn := utl_smtp.open_connection(p_mailhost, p_mailport);
        utl_smtp.helo(l_mail_conn, p_mailhost);
        utl_smtp.mail(l_mail_conn, p_sender);
        utl_smtp.rcpt(l_mail_conn, p_recipient);
        if p_cc is not null then -- mail ccs as well
        utl_smtp.rcpt(l_mail_conn, p_cc);
        end if;
        utl_smtp.data(l_mail_conn, l_message);
        utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN others THEN
         raise_application_error(       -20000,
        
'Failed to send mail due to the following error: ' ||crlf|| sqlerrm);
END
send_email;

end webutils;
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: