UTL_SMTP - Multiple Recipients

From: Subbiah, Nagarajan <Nagarajan.Subbiah_at_aetn.com>
Date: Mon, 5 Dec 2005 11:37:10 -0500
Message-ID: <30462D80AA52E74698512ADCC4F7EAA32D74C442@exchange.aetvn.com>

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

Received on Mon Dec 05 2005 - 11:44:22 CST