Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Oracle / Oracle Server / September 2007

Tip: Looking for answers? Try searching our database.

utl_smtp.rcpt and multiple emails

Thread view: 
Enable EMail Alerts  Start New Thread
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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.