UTL_SMTP - Multiple Recipients

  • From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 5 Dec 2005 11:37:10 -0500

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


Other related posts: