Send mail in oracle with UTF-8 encoding in subject

Question

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

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

My code looks like this:

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.

Instead of

TEST(ČŽŠ)ščžž

i get

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

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);

Any idea?

EDIT:

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:

Sent from Outlook:

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

Sent from Oracle:

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

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...

I tested this with 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

returns

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

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.

Answer 1

For me this one is working:

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

UTL_SMTP.WRITE_DATA(l_mail_conn, 'Subject: '||Subject||UTL_TCP.CRLF);
...
UTL_SMTP.write_raw_data(l_mail_conn,utl_raw.cast_to_raw(p_message));

Try an additional UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF); before UTL_SMTP.close_data

You have some CRLF instead of UTL_TCP.CRLF - is this the same? Better use always UTL_TCP.CRLF.

Then you have a extra (or missing) double-quote in UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset=UTF-8"' || UTL_TCP.crlf);

Review your code carefully.

Update

Maybe try

UTL_SMTP.WRITE_DATA(l_mail_conn, 'Subject: =?UTF-8?Q?'||UTL_ENCODE.TEXT_ENCODE(Subject, NULL, UTL_ENCODE.QUOTED_PRINTABLE)||'?=' ||UTL_TCP.CRLF );

TEXT_ENCODE inserts a NEWLINE each 76 characters, however according Base64: MIME they should be ignored.