Database Forum / Oracle / Oracle Server / September 2007
utl_smtp.rcpt and multiple emails
|
|
Thread rating:  |
jobs - 26 Sep 2007 15:57 GMT 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 := 'me@me.com,you@you.com; --does not --esendto := 'me@me.com;you@you.com; --does not --esendto := 'me@me.com you@you.com; --does not --esendto := 'me@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, 'job@mydomain.com'); utl_smtp.rcpt(c, v_rcpt); utl_smtp.open_data(c); header('From', '"JOB" <job@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.
TD - 26 Sep 2007 18:32 GMT 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@you.org', --p_to => 'you@you.org,someone@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 ); 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@you.org', p_from => 'you@you.org', 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@you.org', p_from => 'you@you.org', 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@you.org', p_from => 'you@you.org', 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 *******************************************************************************
> 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 [quoted text clipped - 43 lines] > > Thanks for any help or information. Vladimir M. Zakharychev - 27 Sep 2007 18:08 GMT > 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 [quoted text clipped - 43 lines] > > Thanks for any help or information. Official SMTP protocol specification, RFC-2821, which can be found at http://www.ietf.org/rfc/rfc2821.txt, holds the key: the RCPT command accepts single forward-path (which is normally a mailbox.) To specify more than one recipient for a message, RCPT must be issued once for each target mailbox. This means that you need to break down your list of addresses into an array and then
ix := myarray.first; while ix is not null loop utl_mail.rcpt(c, myarray(ix)); ix := myarray.next(ix); end loop;
In addition to that, you should ensure that each forward-path is correctly formed as per standard specification. This is not absolutely necessary, but some strict implementations of the protocol may reject RCPT arguments not formed to the very letter of the standard (others do accept them and reformat properly for further transfer.)
Hth, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
jobs - 28 Sep 2007 15:12 GMT On Sep 27, 1:08 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc...@gmail.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 [quoted text clipped - 68 lines] > > - Show quoted text - jobs - 28 Sep 2007 15:13 GMT thank you.
now if only i can figure out how to split a string in 9i. This apparently is a 10 g thing.
myarray := split(sendto, ',');
Vladimir M. Zakharychev - 28 Sep 2007 16:41 GMT > thank you. > > now if only i can figure out how to split a string in 9i. This > apparently is a 10 g thing. > > myarray := split(sendto, ','); Nothing very complex here, writing a split function is pretty trivial in PL/SQL (assuming that myarray is a PL/SQL collection, if it's an SQL collection type (a nested table or a VARRAY) then you will need to extend the array before adding value to it using array.extend() method):
function split( s in varchar2, delim in varchar2 := ',') return myarray is rv myarray; ix pls_integer := 1; -- index into the array d_pos pls_integer; -- delimiter position l_s varchar2(32765) := s; -- working local copy of the string begin loop exit when l_s is null; -- exit when the source string is empty d_pos := instr(l_s,delim); -- next delimiter position if d_pos > 0 then -- found it rv(ix) := substr(l_s,1,d_pos-1); -- extract next token ix := ix + 1; -- advance the array index (sometimes I miss C ++ syntax... :)) l_s := substr(l_s, d_pos+1); -- trim the token from the source string else rv(ix) := l_s; -- there's only one token in the source string l_s := null; -- indicate that we're done end if; end loop; return rv; end split;
If you are going to create this function standalone (that is, not in a package,) then I'd suggest that you use a nested table type for output - you'll have an additional benefit of being able to query this function as if it was a table ( SELECT * FROM TABLE(split(...)) ) and make it pipelined to speed up things a bit. See the docs for details on these features if you are not familiar with them (TABLE() function and pipelined functions.)
Hth, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
DA Morgan - 28 Sep 2007 23:04 GMT >> thank you. >> [quoted text clipped - 48 lines] > N-Networks, makers of Dynamic PSP(tm) > http://www.dynamicpsp.com Or perhaps even easier to use with a pipelined table function.
 Signature Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
|
|
|