sql - Oracle - 发送带有 excel 附件的电子邮件
问题描述
我想使用 PLSQL 通过电子邮件发送 excel 文件
我使用 XLSX_BUILDER_PKG 创建 excel 文件,它工作正常。它可以正确创建 excel 文件。
https://github.com/commi235/xlsx_builder/blob/master/xlsx_builder_pkg.pks
declare
excel BLOB := empty_blob();
begin
xlsx_builder_pkg.clear_workbook;
xlsx_builder_pkg.new_sheet;
xlsx_builder_pkg.cell( 1, 4, 'Start date:', p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ),p_fillId => xlsx_builder_pkg.get_fill('solid','99CCFF'), p_fontId => xlsx_builder_pkg.get_font( 'Calibri',p_bold => true,p_fontsize => 12) );
xlsx_builder_pkg.cell( 1, 5, sysdate,p_fillId => xlsx_builder_pkg.get_fill('solid','99CC99'),p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ) );
xlsx_builder_pkg.cell( 4, 4, 'End date:', p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ),p_fillId => xlsx_builder_pkg.get_fill('solid','99CCFF'), p_fontId => xlsx_builder_pkg.get_font( 'Calibri',p_bold => true,p_fontsize => 12) );
xlsx_builder_pkg.cell( 4, 5, sysdate ,p_fillId => xlsx_builder_pkg.get_fill('solid','99CC99'),p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ));
xlsx_builder_pkg.save('EXCEL_FILES', 'emp8.xls');
excel := xlsx_builder_pkg.finish;
test_send_mail('my@mail.com',
'my@mail.com',
'Test',
'Test',
'excel.xls',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
excel,
'myhost',
25);
end;
然后我想将 excel 文件作为附件发送,我使用 xlsx_builder_pkg.finish 函数来获取 BLOB,当我发送电子邮件时,我得到了附件,但 excel 文件已损坏。
CREATE OR REPLACE PROCEDURE test_send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
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, '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, 'Reply-To: ' || p_from || 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/mixed; 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_attach_name 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: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
我使用了不同的 MIME 类型,但是使用 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 我有损坏的文件(散列信息)和使用 'application/vnd.ms-excel' 我有文件我打不开
解决方案
将此与我提供的代码进行比较,您似乎缺少 mime 标头属性 Content-Transfer-Encoding,它应该是 Base64
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: Base64' || UTL_TCP.crlf);
如果附件变得非常大,或者代码扩展为允许多个附件,要考虑的另一件事是在打开与 SMTP 服务器的连接之前进行 base64 编码。否则,在转换/写入时,您可能会因为长时间保持会话而超时。
推荐阅读
- html - 如何在表格行中显示日期时间弹出窗口
- angularjs - 如何使用angularJs上传带有文件内容的文件
- java - 扩展 AbstractTestNGSpringContextTests 时,抽象基类中的自动装配弹簧依赖项始终为空
- django - 错误:提交反应表单时操作可能没有未定义的“类型”属性
- python - 找不到页面(404):没有帖子与给定的查询匹配。
- android - 程序类型已经存在:Android.arch.lifecycle.LiveData$1
- python - 无法在 Python 中使用 pandas 系列将字符串转换为浮点数
- angularjs - 获取 TypeError:XYZ 不是 Angular 中的函数
- bash - Bash:在 for 循环中的 echo $variable 之后附加字符
- bootstrap-4 - 保持按钮对齐独立于文本长度