Re: utl_smtp.rcpt and multiple emails

On Sep 26, 6:57 pm, 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.

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 Received on Thu Sep 27 2007 - 12:08:53 CDT