Re: utl_smtp.rcpt and multiple emails
- From: TD <todwith1d@xxxxxxxxx>
- Date: Wed, 26 Sep 2007 17:32:34 -0000
comma should work as far as I know. we use a modified version. see
if this helps... there is some testing code as well.
find all instances of the word "you" to edit for your environment.
Start
*******************************************************************************
create or replace procedure html_email(
p_to in varchar2,
p_bcc in varchar2 default null,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2
)
is
/*
Taken from AskTom at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1739411218448
10-27-2006 - Added exception handling to close connection.
5-23-2007 - Handling instances where p_text or p_html is null.
*/
CRLF constant varchar2(2) := chr(13) || chr(10);
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email
message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname,
p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );
if p_bcc is not null then
utl_smtp.rcpt( l_connection, p_bcc );
end if;
l_temp := l_temp || 'MIME-Version: 1.0' || CRLF;
l_temp := l_temp || 'To: ' || p_to || CRLF;
l_temp := l_temp || 'Bcc: ' || p_bcc || CRLF;
l_temp := l_temp || 'From: ' || p_from || CRLF;
l_temp := l_temp || 'Subject: ' || p_subject || CRLF;
l_temp := l_temp || 'Reply-To: ' || p_from || CRLF;
l_temp := l_temp || 'Content-Type: multipart/alternative;
boundary=' ||
chr(34) || l_boundary || chr(34) || CRLF;
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
if p_text is not null then
----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || CRLF;
l_temp := l_temp || 'content-type: text/plain; charset=us-
ascii' || CRLF || CRLF;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(p_text), l_offset, p_text);
end if;
if p_html is not null then
----------------------------------------------------
-- Write the HTML boundary
l_temp := CRLF||CRLF||'--' || l_boundary || CRLF;
l_temp := l_temp || 'content-type: text/html;' || CRLF ||
CRLF;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);
end if;
----------------------------------------------------
-- Write the final html boundary
l_temp := CRLF || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) -
l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
Exception
When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error Then
Begin
Utl_Smtp.Quit(l_Connection); --This may fail, if the SMTP
server is down.
Raise;
End;
End;
/
/*
Begin
html_email(
p_to => 'you@xxxxxxx',
--p_to => 'you@xxxxxxx,someone@xxxxxxxxxxx', -- CORRECT MULTIPLE
ADDRESS FORMAT
p_subject => 'test e-mail',
p_text => 'this is a text test body',
p_html => 'this is a html test body',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
);
Exception
When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error Then Begin
Dbms_Output.Put_Line('SqlCode: ' || SqlCode);
Dbms_Output.Put_Line('SqlErrm: ' || SqlErrm);
End;
End;
/
Declare
l_Big Varchar2(32767);
Begin
For ii in 1..32767 Loop
l_Big := l_Big || 'x';
End Loop;
html_email(
p_to => 'you@xxxxxxx',
p_from => 'you@xxxxxxx',
p_subject => 'big e-mail',
p_text => l_Big,
p_html => '',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
);
Exception
When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error Then Begin
Dbms_Output.Put_Line('SqlCode: ' || SqlCode);
Dbms_Output.Put_Line('SqlErrm: ' || SqlErrm);
End;
End;
/
Declare
l_Big Varchar2(32767);
Begin
For ii in 1..32767 Loop
l_Big := l_Big || 'x';
End Loop;
html_email(
p_to => 'you@xxxxxxx',
p_from => 'you@xxxxxxx',
p_subject => 'big e-mail',
--p_text => l_Big,
--p_text => 'some non-html text',
--p_html => '',
p_html => 'some html text',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
);
Exception
When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error Then Begin
Dbms_Output.Put_Line('SqlCode: ' || SqlCode);
Dbms_Output.Put_Line('SqlErrm: ' || SqlErrm);
End;
End;
/
Begin
html_email(
p_to => 'you@xxxxxxx',
p_from => 'you@xxxxxxx',
p_subject => 'no body',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
);
Exception
When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error Then Begin
Dbms_Output.Put_Line('SqlCode: ' || SqlCode);
Dbms_Output.Put_Line('SqlErrm: ' || SqlErrm);
End;
End;
/
*/
show errors
End
*******************************************************************************
On Sep 26, 10:57 am, jobs <j...@xxxxxxxxxx> wrote:
I'm trying to email several addresses. My script works great when it's
only going to one address, but if I attempt to send to mulitiple
nothing get's sent. I've tried delimiting them with space, comma and
semicolon:
usage:
declare
esendto varchar2(100);
esubject varchar2(100);
ebody varchar2(2000);
begin
esendto := '...@xxxxxx,y...@xxxxxxx; --does not
--esendto := '...@xxxxxx;y...@xxxxxxx; --does not
--esendto := '...@xxxxxx y...@xxxxxxx; --does not
--esendto := '...@xxxxxx; --works
esubject := 'Test email from Oracle ...................... long
subject';
ebody :='10'|| utl_tcp.CRLF;
EMAIL(esendto,esubject,ebody);
end;
the email procedure::
CREATE OR REPLACE PROCEDURE "EMAIL"(v_rcpt in varchar2,
v_subject in varchar2,
texto in varchar2) as
c utl_smtp.connection;
PROCEDURE header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
c := utl_smtp.open_connection('mysmtp.mydomain.com');
utl_smtp.helo(c, 'mysmtp.mydomain.com');
utl_smtp.mail(c, '...@xxxxxxxxxxxx');
utl_smtp.rcpt(c, v_rcpt);
utl_smtp.open_data(c);
header('From', '"JOB" <j...@xxxxxxxxxxxx>');
header('To', v_rcpt);
header('Subject', v_subject);
utl_smtp.write_data(c, utl_tcp.crlf || texto || utl_tcp.CRLF);
utl_smtp.close_data(c);
utl_smtp.quit(c);
END;
Thanks for any help or information.
.
- References:
- utl_smtp.rcpt and multiple emails
- From: jobs
- utl_smtp.rcpt and multiple emails
- Prev by Date: SQL Developer settings - Set Define Off
- Next by Date: Re: RMAN Log Analyzer
- Previous by thread: utl_smtp.rcpt and multiple emails
- Next by thread: Re: utl_smtp.rcpt and multiple emails
- Index(es):
Relevant Pages
|
