如何将 Oracle PL/SQL 包中的电子邮件发送给多个收件人？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17732220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sent email in Oracle PL/SQL package to multiple receivers?
提问by Deep in Development
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.
如何将 Oracle PL/SQL 包中的电子邮件发送给多个收件人？我在 oracle 包中有以下 pl/sql 程序，它仅适用于一个接收器。我需要改进它的功能，让它可以同时向多个接收者发送电子邮件，比如“To: David Festool; Peter Makita; John Dewalt”。任何机构可以帮助我将不胜感激！请提供我修改后的代码。
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 protected]');
utl_smtp.rcpt(c, p_recip);
utl_smtp.open_data(c);
send_header('From', '"Title" <[email protected]');
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;
--------------------------------------------------------------
回答by Alex Poole
You need to call utl_smtp.rcptmultiple times, once for each recipient; you can't give a list of values in one call.
您需要调用utl_smtp.rcpt多次，每个收件人一次；您无法在一次通话中提供值列表。
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 TOheader is a separate issue; if I recall correctly, that is really just for display, and having an address as a rcptbut notin the TO(or CC) header is how BCC is implemented. Citation needed though...
所述TO报头是一个单独的问题; 如果我没有记错，这是真的只是用于显示，其地址为一个rcpt，但不是在TO（或CC）头是BCC是如何实现的。虽然需要引用...
Here's an old AskTom article demonstrating this. jonearles suggestion to use UTL_MAIL should be investigated though.
这是一篇旧的 AskTom 文章，演示了这一点。不过，应调查 jonearles 建议使用 UTL_MAIL。
回答by UTL_MAIL
The format is:
格式为：
UTL_MAIL.SEND (sender, recipientlist, cc, bcc, subject, Message, mime_type, priority)
The recipientlist, cc, and bcc parameters are all comma-separated lists of recipient, copy to, and blind copy e-mail addresses.
收件人列表、抄送和密件抄送参数都是以逗号分隔的收件人、复制到和密送电子邮件地址列表。
The sender, subject, message, and mime_type parameters are all single item fields.
sender、subject、message 和 mime_type 参数都是单项字段。
回答by Shahidul Islam Molla
Just run below procedure with change code:
只需使用更改代码运行以下过程：
v_Mail_Host VARCHAR2(50) := 'uacemail.rxcorp.com'; -- your host ip or name
v_Mail_Host VARCHAR2(50) := 'uacemail.rxcorp.com'; -- 您的主机 IP 或名称
Execute:
执行：
begin
prc_email_send( '[email protected]', -- Mail From
'[email protected]',---Recipient
'[email protected];[email protected]',-- Cc List
'This is mail subject ',
'This is mail body' );
end;
/
开始
prc_email_send( '[email protected]', -- 邮件发件人
'[email protected]',--- 收件人
'[email protected];[email protected]',-- 抄送列表
'这是邮件主题 ',
'这是邮件正文' );
结尾;
/
Procedure Code:
程序代码：
Create or replace procedure prc_email_send(
v_From VARCHAR2,
v_Recipient VARCHAR2,
v_cc_list varchar2,
v_Subject VARCHAR2,
v_Mail_body VARCHAR2
)
is
v_Mail_Host VARCHAR2(50) := 'uacemail.rxcorp.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
CC_parties varchar2(2000);
begin
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
for i in (SELECT LEVEL AS id, REGEXP_SUBSTR(v_cc_list, '[^;]+', 1, LEVEL) AS cc_email_name
FROM dual
CONNECT BY REGEXP_SUBSTR(v_cc_list, '[^;]+', 1, LEVEL) IS NOT NULL) loop
CC_parties := CC_parties||';'|| i.cc_email_name;
utl_smtp.Rcpt(v_Mail_Conn,i.cc_email_name);
end loop;
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'Cc: ' || CC_parties|| crlf ||
'Content-Type: text/html;' ||crlf ||
v_Mail_body);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.put_line (
SUBSTR (
'Unable to send mail to recipients. Error message: '
|| SQLERRM
|| CHR (10)
|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (),
1,255));
UTL_SMTP.quit (v_Mail_Conn);
UTL_TCP.close_all_connections;
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
NULL;
END;
END;
This is working fine for myself
这对我自己来说很好用
