如何将Oracle PL/SQL包中的电子邮件发送给多个接收者? [英] How to sent email in Oracle PL/SQL package to multiple receivers?

问题描述

如何将Oracle PL/SQL包中的电子邮件发送给多个接收者?我在oracle软件包中有下面的pl/sql过程，它仅适用于一个接收者.我需要改进它的功能，使其可以同时向多个收件人发送电子邮件，例如收件人:David Festool； Peter Makita； John Dewalt".任何身体都可以帮助我，将不胜感激！请提供修改后的代码.

How to sent email in Oracle PL/SQL package to multiple receivers? I have below pl/sql procedure within an oracle package, it works only for one receiver. I need to improve it functional to let it can send email to multiple receivers at same time like "To: David Festool; Peter Makita; John Dewalt". Any body can help me out will be great appreciate! Please provide me modified code.

procedure email(p_recip   in varchar2,
                p_subject in varchar2,
                p_message in varchar2) is

  c   utl_smtp.connection;
  msg varchar2(4000);

  procedure send_header(name in varchar2, header in varchar2) as
  begin
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.crlf);
  end;
begin
  --Open SMTP connection
  c := utl_smtp.open_connection('ExchangeServerName');

  -- Write SMTP header
  utl_smtp.helo(c, 'ExchangeServerName');
  utl_smtp.mail(c, '[e-mail]');
  utl_smtp.rcpt(c, p_recip);
  utl_smtp.open_data(c);
  send_header('From', '"Title" <[e-mail]');
  send_header('To', p_recip);
  send_header('Subject', p_subject);
  send_header('Mime-Version', '1.0');
  send_header('Content-Type', 'multipart/mixed; boundary="DMW.Boundary.605592468"');

  -- Write MIME boundary line for the message body
  msg := utl_tcp.crlf || '--DMW.Boundary.605592468' || utl_tcp.crlf ||
         'Content-Type: text/plain' || utl_tcp.crlf ||
         'Content-Transfer-Encoding: 7bit' || utl_tcp.crlf ||
         utl_tcp.crlf;
  utl_smtp.write_data(c, msg);

  -- Write message body
  utl_smtp.write_data(c, p_message || utl_tcp.crlf);

  -- Clean up
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
exception
  when utl_smtp.transient_error or utl_smtp.permanent_error then
    begin
      utl_smtp.quit(c);
    exception
      when utl_smtp.transient_error or utl_smtp.permanent_error then
        null;
        -- When the SMTP server is down or unavailable, we don't have
      -- a connection to the server. The QUIT call will raise an
      -- exception that we can ignore.
    end;

    raise_application_error(-20000, 'Failed to send mail due to the following error: ' ||
                             sqlerrm);
end;
--------------------------------------------------------------

推荐答案

您需要多次呼叫utl_smtp.rcpt，每个接收者一次；您无法在一次调用中提供值列表.

You need to call utl_smtp.rcpt multiple times, once for each recipient; you can't give a list of values in one call.

来自UTL_SMTP.RCPT文档:

要将消息发送给多个收件人，请多次调用此例程 时代.每次调用都计划将邮件发送到单个电子邮件地址.

To send a message to multiple recipients, call this routine multiple times. Each invocation schedules delivery to a single e-mail address.

这意味着您不能真正传递一个字符串，除非您很乐意将各个地址解析出来.传递一个值数组可能会更容易.

That means you can't really pass a string of names, unless you're happy to parse the individual addresses out; it would be easier to pass an array of values, probably.

TO标头是一个单独的问题；如果我没记错的话，那实际上是为了显示，并且在TO(或CC)标头中有一个rcpt但不是 的地址是实现BCC的方法.虽然需要引用...

The TO header is a separate issue; if I recall correctly, that is really just for display, and having an address as a rcpt but not in the TO (or CC) header is how BCC is implemented. Citation needed though...

这是旧的AskTom证明这一点的文章.但是jonearles建议使用UTL_MAIL.

Here's an old AskTom article demonstrating this. jonearles suggestion to use UTL_MAIL should be investigated though.

这篇关于如何将Oracle PL/SQL包中的电子邮件发送给多个接收者?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！