| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: utl_smtp.rcpt and multiple emails
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
/*
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;
----------------------------------------------------
if p_text is not null then
if p_html is not null then
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_at_you.org',
--p_to => 'you_at_you.org,someone_at_someone.com', -- 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
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;
Declare
l_Big Varchar2(32767);
Begin
For ii in 1..32767 Loop
l_Big := l_Big || 'x';
End Loop;
html_email(
p_to => 'you_at_you.org',
p_from => 'you_at_you.org',
p_subject => 'big e-mail',
p_text => l_Big,
p_html => '',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
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;
Declare
l_Big Varchar2(32767);
Begin
For ii in 1..32767 Loop
l_Big := l_Big || 'x';
End Loop;
html_email(
p_to => 'you_at_you.org',
p_from => 'you_at_you.org',
p_subject => 'big e-mail',
--p_text => l_Big,
p_html => 'some html text',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
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;
Begin
html_email(
p_to => 'you_at_you.org',
p_from => 'you_at_you.org',
p_subject => 'no body',
p_smtp_hostname => 'mail.you.org',
p_smtp_portnum => 25
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;
*/
show errors
End
On Sep 26, 10:57 am, jobs <j..._at_webdos.com> 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 := '..._at_me.com,y..._at_you.com; --does not
> --esendto := '..._at_me.com;y..._at_you.com; --does not
> --esendto := '..._at_me.com y..._at_you.com; --does not
> --esendto := '..._at_me.com; --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, '..._at_mydomain.com');
> utl_smtp.rcpt(c, v_rcpt);
> utl_smtp.open_data(c);
> header('From', '"JOB" <j..._at_mydomain.com>');
> 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.
Received on Wed Sep 26 2007 - 12:32:34 CDT
 |
 |