UTL_SMTP - Multiple Recipients 2005-12-05 - By Subbiah, Nagarajan
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 (See ORA-20000.ora-code.com): Failed to send mail due to the following error:
ORA-29279 (See ORA-29279.ora-code.com): SMTP permanent error: 553 malformed address:
Nsubbiah@(protected),ess.admin@(protected)
ORA-06512 (See ORA-06512.ora-code.com): 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
|
|
