使用主题中的UTF-8编码在oracle中发送邮件 [英] Send mail in oracle with UTF-8 encoding in subject

问题描述

我需要发送一封来自Oracle的电子邮件，其中包含诸如ŠŽČĐĆšžčćđ等主题的特殊字符.

I need to send an email from Oracle, with special characters in a subject, like ŠŽČĐĆšžčćđ.

我在这里找到了一些信息: https://journal.missiondata.com/sending-utf- 8-email-with-oracle-d276a05cf94b

I found some information here: https://journal.missiondata.com/sending-utf-8-email-with-oracle-d276a05cf94b

我的代码如下:

UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, p_sender);
UTL_SMTP.rcpt (l_mail_conn, p_recipient);
UTL_SMTP.open_data (l_mail_conn);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'From' || ': ' || p_sender || CRLF);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'To' || ': ' || p_recipient || CRLF);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'Subject: =?UTF-8?Q?' || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(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: text/plain; charset=UTF-8"' || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Transfer-Encoding: quoted-printable '|| UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);
UTL_SMTP.WRITE_RAW_DATA(l_mail_conn, UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message)));
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);

如果仅使用大写字母(ČŽŠĆ)，则一切正常. 但是，如果主题上至少有一个小写字母，它将被破坏.

If I use uppercase letters (ČŽŠĆ) only, everything works great. But, if there is at least one lowercase letter on the subject, it gets corrupted.

代替

TEST(ČŽŠ)ščžž

TEST(ČŽŠ)ščžž

我明白了

=?UTF-8?Q?TEST(= C4 = 8C = C5 = BD = C5 = A0)= C5 = A1 =

=?UTF-8?Q?TEST(=C4=8C=C5=BD=C5=A0)=C5=A1=

Oracle DB 12c使用AL32UTF8字符集.

Oracle DB 12c uses AL32UTF8 character set.

我还尝试了以下代码，该代码适用于大多数用户，但是有些邮件服务器/邮件客户端无法正确检测主题编码.

I also tried the following code, which works for a majority of users, but there are some mail servers/mail clients which do not detect subject encoding correctly.

UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, p_sender);
UTL_SMTP.rcpt (l_mail_conn, p_recipient);
UTL_SMTP.open_data (l_mail_conn);

UTL_SMTP.WRITE_DATA(l_mail_conn, 'From' || ': ' || p_sender || CRLF);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'To' || ': ' || p_recipient || CRLF);
UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Subject' || ': ' || p_subject ));
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="UTF-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_raw_data(l_mail_conn,utl_raw.cast_to_raw(p_message));
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);

有什么主意吗?

我试图从Outlook和Oracle发送具有相同主题的电子邮件.我使用BASE64编码. Outlook中的两个Internet标头都相同， 但仅适用于主题ŠŽČŽšžčćđ之类的主题"

I tried to send an email with the same subject from Outlook and from Oracle. I used BASE64 encoding. Both internet headers in Outlook are the same, but only for subject "subject like ŠŽČĐĆšžčćđ"

Subject: =?utf-8?B?c3ViamVjdCBsaWtlIMWgxb3EjMSQxIbFocW+xI3Eh8SR?=

当我将主题更改为像ŠšČĐĆšžčćđ这样的主题:(测试Ščž)"时，事情就会出错:

When I change subject to "subject like ŠŽČĐĆšžčćđ: (test Ščž)" things get wrong:

从Outlook发送:

Sent from Outlook:

Subject: =?utf-8?B?
c3ViamVjdCBsaWtlIMWgxb3EjMSQxIbFocW+xI3Eh8SROiAodGVzdCDFoMSN?=
 =?utf-8?B?xb4p?=

从Oracle发送:

Subject: =?utf-8?B?
c3ViamVjdCBsaWtlIMWgxb3EjMSQxIbFocW+xI3Eh8SROiAodGVzdCDFoMSNxb4p

似乎有一些新的换行/回车符，这会引起麻烦. Outlook放置=?utf-8?B?在每一行的前面，Oracle都不...

It seems there are some new line/carriage return characters, which cause troubles. Outlook puts =?utf-8?B? in front of every line, Oracle doesn't...

我用SQL进行了测试

select 'Subject: =?utf-8?B?' || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('subject like ŠŽČĐĆšžčćđ: (test Ščž)'))) || '?=' 
from dual

返回

Subject: =?utf-8?B?c3ViamVjdCBsaWtlIMWgxb3EjMSQxIbFocW+xI3Eh8SROiAodGVzdCDFoMSNxb4p
?=

我用replace修复了它

I fixed it with replace:

UTL_SMTP.WRITE_DATA(l_mail_conn, 'Subject: =?utf-8?B?' ||
                            replace(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('subject like ŠŽČĐĆšžčćđ: (test Ščž)'))),CHR(13) || CHR(10),'?=' || CHR(13) || CHR(10)||' =?utf-8?B?')
                            || '?=' || UTL_TCP.CRLF);

现在可以使用...但是我希望有一个更好的解决方案.

It works now... but I was hoping for a better solution.

推荐答案

主题解决方案:

UTL_SMTP.WRITE_raw_DATA(c, UTL_RAW.cast_to_raw('Subject: '||REPLACE(utl_encode.MIMEHEADER_ENCODE(Subj, 'utf8'),CHR(13) || CHR(10),'') || utl_tcp.CRLF));

这篇关于使用主题中的UTF-8编码在oracle中发送邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！