RE: UTL_SMTP - Multiple Recipients

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 5 Dec 2005 13:48:08 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6502EF6007@25exch1.vicorpower.vicr.com>

Raj,

        You may notice that this procedure does it's own internal e-mail logging via utl_file. You can turn it on or off as you wish. Or even change it from a file to a table. Your choice.

-----Original Message-----
From: Subbiah, Nagarajan [mailto:Nagarajan.Subbiah_at_aetn.com] Sent: Monday, December 05, 2005 1:32 PM
To: Goulet, Dick; oracle-l_at_freelists.org Subject: RE: UTL_SMTP - Multiple Recipients

Thanks, List. I made the change and Its working now.

Is there any pre-defined e-mail logging feature can be enabled while sending
the e-mail or Should this to be customized like inserting the error/mail log
in a table by adding the steps in this procedure?

Raja.

-----Original Message-----
From: Goulet, Dick [mailto:DGoulet_at_vicr.com] Sent: Monday, December 05, 2005 1:01 PM
To: Nagarajan.Subbiah_at_aetn.com; oracle-l_at_freelists.org Subject: RE: UTL_SMTP - Multiple Recipients

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_at_vicr.com>');   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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Subbiah, Nagarajan
Sent: Monday, December 05, 2005 11:37 AM To: 'oracle-l_at_freelists.org'
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_at_aetvn.com,ess.admin_at_aetn.com
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;

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

Received on Mon Dec 05 2005 - 14:47:32 CST