Cyrillic characters are distorted when sending a letter

Question

When ending a letter to Outlook mail from an Oracle DBMS that contains Cyrillic characters, the output is question marks. I do not understand how to convert the string so that the text is readable. I ship using the following method:

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
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.data(l_mail_conn, 'Привет мир!' || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;

I use Oracle Database 10g Release 10.2.0.3.0 - Production

Answer 1

You need to add lines like this:

SELECT UTL_I18N.MAP_CHARSET(VALUE)
INTO Charset
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET';

UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Type: text/plain; charset='||Charset || UTL_TCP.CRLF);

By this you tell the mail server/client which character set is used in the mail.

The mail subject does not natively support Non-ASCII characters. You need to encode it like this (see. RFC1342):

UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE('Привет мир!', 'AL32UTF8', UTL_ENCODE.BASE64) ||'?='||UTL_TCP.CRLF);

See how to export data from log table to email body in oracle to get a more advanced and complete solution.