正在尝试在oracle UTL_MAIL中发送多个bcc

Question

我正试图通过Oracle软件包/过程发送多个电子邮件地址。当只向、抄送、密件抄送发送单个邮件时，我有一个工作过程。(这意味着我可以发送其中一个。(但是，尽管有一个循环(从逗号分隔的列表中(生成正确的单个电子邮件地址，但该过程失败了。我希望有人能指出我的代码问题。

注意。包的代码有效；只有函数two-write-multple-rcpt行没有。

当我使用单独的代码块发送消息时(见下文(，我会收到以下错误：错误报告-ORA-29279:SMTP永久错误：550内部错误：无效参数

首先，这是我的process_recipients过程代码：

PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
p_list      IN     VARCHAR2) IS

a    DBMS_UTILITY.UNCL_ARRAY;
len  PLS_INTEGER;
BEGIN
DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
FOR i IN 1..a.COUNT LOOP
--dbms_output.put_line( a(i) );
UTL_SMTP.rcpt(p_mail_conn, a(i) );
END LOOP;
END process_recipients;

以下是send_email过程中的相关代码，该过程将调用proccess_recipients。。。

PROCEDURE send_email (
p_mail_server   IN VARCHAR2
, p_mail_port       IN VARCHAR2
, p_from            IN VARCHAR2
, p_to              IN VARCHAR2
, p_cc              IN VARCHAR2 DEFAULT NULL
, p_bcc             IN VARCHAR2 DEFAULT NULL
, p_subject         IN VARCHAR2
, p_html_msg            IN VARCHAR2 DEFAULT NULL    
, p_text_msg            IN VARCHAR2 DEFAULT NULL
) IS
l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';    

BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_mail_server,p_mail_port);
UTL_SMTP.helo(l_mail_conn, p_mail_server);
UTL_SMTP.mail(l_mail_conn, p_from);
--UTL_SMTP.rcpt(l_mail_conn, p_to);
process_recipients(l_mail_conn, p_to);   
IF p_cc IS NOT NULL THEN
UTL_SMTP.rcpt(l_mail_conn, p_cc);
END IF;
IF p_bcc IS NOT NULL THEN
UTL_SMTP.rcpt(l_mail_conn, p_bcc);
END IF;

以下是实际执行包/过程的代码：

DECLARE
-- l_tab dbms_utility.lname_array;
html_message VARCHAR2(2000);
BEGIN
--GZ_SEND_EMAIL.p_send_v_test_email_address('smtp.isu.edu','25','[e-mail]','<em>This</em> is a 3nd test message');
html_message := '<strong>Curabitur a arcu id erat fermentum dapibus et at leo.</strong> <p>Nunc placerat finibus pellentesque. Curabitur id ligula ac dui dictum bibendum. Suspendisse eget neque risus. </p>';

GZ_SEND_EMAIL.send_email(
p_mail_server => 'smtp.isu.edu'
, p_mail_port => '25'
, p_from => '[e-mail]'
, p_to => '[e-mail],[e-mail]'
, p_cc => '[e-mail]'
, p_bcc => NULL
, p_subject => 'Come join me for lunch at 11:07.'
, p_html_msg => html_message
, p_text_msg => 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut vel justo quis felis pellentesque iaculis. Sed sed tristique felis, non condimentum erat. Duis maximus, ligula quis imperdiet euismod, velit sem consectetur erat, a pellentesque ex dui id massa. Suspendisse a dolor elit.'
);

END;

还有，这是最后一个狙击(不在程序中(，我用来测试我是否真的在发送单个电子邮件地址。(注意。我在这里使用了假电子邮件，但在测试中没有。(

DECLARE
a    DBMS_UTILITY.UNCL_ARRAY;
len  PLS_INTEGER;
p_list VARCHAR2(200);
BEGIN
p_list := '[e-mail],[e-mail],[e-mail],[e-mail]';
DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
FOR i IN 1..a.COUNT LOOP
dbms_output.put_line( a(i) );
END LOOP;
END; 

Answer 1

我这样使用它：

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;
PROCEDURE SendMail(
Subject IN VARCHAR2, 
Message IN OUT CLOB, 
ToMail IN VARCHAR_TABLE_TYPE,   
FromMail IN VARCHAR2, 
FromName IN VARCHAR2,
PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL) IS
SMTP_PORT               CONSTANT INTEGER := 25;
SMTP_SERVER             CONSTANT VARCHAR2(50):= 'mailhost';
MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';
con UTL_SMTP.CONNECTION;
ret UTL_SMTP.REPLY;
BEGIN
-- setup mail header
con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
ret := UTL_SMTP.MAIL(con, FromMail);
FOR i IN ToMail.FIRST..ToMail.LAST LOOP
Recipients := Recipients ||ToMail(i)||',';
ret := UTL_SMTP.RCPT(con, ToMail(i));
END LOOP;
ret := UTL_SMTP.OPEN_DATA(con);
UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);  
UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'MIME-Version: 1.0' || UTL_TCP.CRLF);
...
END;
SendMail(
'Come join me for lunch at 11:07.', 
Message, 
VARCHAR_TABLE_TYPE('[e-mail]', '[e-mail]', '[e-mail]',  '[e-mail]'),   
'[e-mail]', 
'scholar');

如果你想添加密件抄送和/或抄送地址，我认为它也是一样的。

另请参阅如何在oracle 中将数据从日志表导出到电子邮件正文

Answer 2

根据文档，comma_to_table过程生成：

返回PL/SQL表，其值为1..n，n+1为null。

所以您传入了一个由4个值组成的逗号分隔列表，但您得到的表有5行，第五行为null。当您作为收件人发送null时，正是最后一个null元素导致了错误。

您可以看到，如果您查看最终代码段中的a.COUNT值，或者仅查看当前输出，请参阅此db<gt；不停摆弄

您可以将循环更改为

FOR i IN 1..(a.COUNT - 1) LOOP

或者更安全的是——如果你有一个包含空元素的列表——跳过nulls:

FOR i IN 1..a.COUNT LOOP
IF a(i) IS NOT NULL THEN
dbms_output.put_line( a(i) );
END IF;
END LOOP;

您还应该将To:和CC:标头(作为数据部分中的行(与p_to和p_cc字符串一起发送(您可能已经在本过程的其余部分中进行了，但尚未显示(，但不应该发送BCC:标头。这确实是CC和BCC之间的唯一区别；他们都是收件人，但在标题中没有标识后者。

此外，comma_to_table的限制意味着它在这里可能并不合适——电子邮件地址可能超过30个字符，并且可能包括多字节字符。使用集合(如@Wernfried所示(可能更简单。您可能希望将UTL_MAIL包视为UTL_SMTP的替代方案。

Answer 3

这是我的成品包

create or replace PACKAGE BODY GZ_SEND_EMAIL AS

PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
p_list      IN     VARCHAR2) IS

a    DBMS_UTILITY.UNCL_ARRAY;
len  PLS_INTEGER;
BEGIN
DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
FOR i IN 1..a.COUNT-1 LOOP
--dbms_output.put_line( a(i) );
UTL_SMTP.rcpt(p_mail_conn, a(i) );
END LOOP;

END process_recipients;


PROCEDURE send_email (
p_mail_server   IN VARCHAR2
, p_mail_port       IN VARCHAR2
, p_from            IN VARCHAR2
, p_to              IN VARCHAR2
, p_cc              IN VARCHAR2 DEFAULT NULL
, p_bcc             IN VARCHAR2 DEFAULT NULL
, p_subject         IN VARCHAR2
, p_html_msg            IN VARCHAR2 DEFAULT NULL    
, p_text_msg            IN VARCHAR2 DEFAULT NULL
) IS

l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(255) := '----=*#abc1234321cba#*=';    
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_mail_server,p_mail_port);

UTL_SMTP.helo(l_mail_conn, p_mail_server);
UTL_SMTP.mail(l_mail_conn, p_from);
process_recipients(l_mail_conn, p_to);  
--UTL_SMTP.rcpt(l_mail_conn, p_to);
IF p_cc IS NOT NULL THEN
process_recipients(l_mail_conn, p_cc);        
--UTL_SMTP.rcpt(l_mail_conn, p_cc);
END IF;
IF p_bcc IS NOT NULL THEN
process_recipients(l_mail_conn, p_bcc);   
--UTL_SMTP.rcpt(l_mail_conn, p_bcc);
END IF;


UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: '||p_to||UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'CC: ' || p_cc||UTL_TCP.crlf);  
--UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || p_bcc||UTL_TCP.crlf);  --Do not send header for BCC
UTL_SMTP.write_data(l_mail_conn, 'From: '||p_from||UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: '||p_subject||UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_html_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;



UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF||p_text_msg||UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END send_email;

END GZ_SEND_EMAIL;