如何将 Oracle PL/SQL 包中的电子邮件发送给多个收件人?

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

<小时>

过程邮件(varchar2中的p_recip，varchar2 中的 p_subject，varchar2 中的 p_message) 是c utl_smtp.connection;msg varchar2(4000);过程 send_header(varchar2 中的名称，varchar2 中的标头)作为开始utl_smtp.write_data(c, name || ': ' || header || utl_tcp.crlf);结尾;开始--打开SMTP连接c := utl_smtp.open_connection('ExchangeServerName');-- 写 SMTP 头utl_smtp.helo(c, 'ExchangeServerName');utl_smtp.mail(c, 'Email@MyCompany.on.ca');utl_smtp.rcpt(c, p_recip);utl_smtp.open_data(c);send_header('From', '"Title" <Email@MyCompany.on.ca');send_header('To', p_recip);send_header('主题', p_subject);send_header('Mime-Version', '1.0');send_header('Content-Type', 'multipart/mixed;boundary="DMW.Boundary.605592468"');-- 为邮件正文写 MIME 边界线味精:= utl_tcp.crlf ||'--DMW.Boundary.605592468' ||utl_tcp.crlf ||'内容类型:文本/纯文本' ||utl_tcp.crlf ||'内容传输编码:7 位' ||utl_tcp.crlf ||utl_tcp.crlf;utl_smtp.write_data(c, msg);-- 写消息体utl_smtp.write_data(c, p_message || utl_tcp.crlf);-  清理utl_smtp.close_data(c);utl_smtp.quit(c);例外当 utl_smtp.transient_error 或 utl_smtp.permanent_error 然后开始utl_smtp.quit(c);例外当 utl_smtp.transient_error 或 utl_smtp.permanent_error 然后空值;-- 当 SMTP 服务器宕机或不可用时，我们没有-- 与服务器的连接.QUIT 调用将引发-- 我们可以忽略的异常.结尾;raise_application_error(-20000, '由于以下错误，无法发送邮件:' ||sqlerrm);结尾;--------------------------------------------------------------

解决方案

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

来自 UTL_SMTP.RCPT 文档:><块引用>

要向多个收件人发送消息，请多次调用此例程次.每次调用都会安排发送到一个电子邮件地址.

这意味着您不能真正传递名称字符串，除非您乐于解析单个地址；可能会更容易传递一组值.

TO 标头是一个单独的问题；如果我没记错的话，那真的只是为了显示，并且在 TO(或 CC) 标头是 BCC 的实现方式.虽然需要引用...

这是一个旧的 AskTom证明这一点的文章.不过，应调查 jonearles 建议使用 UTL_MAIL.

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, 'Email@MyCompany.on.ca');
  utl_smtp.rcpt(c, p_recip);
  utl_smtp.open_data(c);
  send_header('From', '"Title" <Email@MyCompany.on.ca');
  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;
--------------------------------------------------------------

解决方案

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

From the UTL_SMTP.RCPT documentation:

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.

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...

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