问题描述
如何将 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.
这篇关于如何将 Oracle PL/SQL 包中的电子邮件发送给多个收件人?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！