Forum Stats

  • 3,874,081 Users
  • 2,266,674 Discussions
  • 7,911,721 Comments

Discussions

Email sent through UTL_SMTP loses diacritics ?

Tomeo
Tomeo Member Posts: 303
edited Aug 13, 2013 7:06AM in SQL & PL/SQL
Hi folks,

I use a below piece of code to send an emails from database. It works fine, except we are losing a special characters - in Latin alphabet, resp. in czech language. Any idea why?
declare
    c_smtp_host varchar2(500) := 'mail.smtpserver.cz';
    c_smtp_port number := 25;
    p_to       VARCHAR2(100) := '[email protected]';
    p_from     VARCHAR2(100) := '[email protected]';
    p_subject  VARCHAR2(100) := 'Email test - ěščřž';
    p_text_msg clob := 'Email test - ěščřž';
    p_html_msg clob := '<html>Email test - ěščřž<br>Here are some special characters: éíáýžřčšěů</html>';
    l_mail_conn UTL_SMTP.connection;
    l_boundary  VARCHAR2(50) := '----=*#abc1234321cba#*=';
  BEGIN
    l_mail_conn := UTL_SMTP.open_connection(c_smtp_host, c_smtp_port);
    UTL_SMTP.helo(l_mail_conn, c_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/alternative; boundary="'||l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
    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,
                          'Content-Type: text/html; charset="UTF-8"' ||
                          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,'--' || l_boundary || '--' || UTL_TCP.crlf);
    UTL_SMTP.close_data(l_mail_conn);
  
    UTL_SMTP.quit(l_mail_conn);
  END;
A received email look like:
Email test - escrz
Here are some special characters: eiayzrcseu 
Instead of:
Email test - ěščřž
Here are some special characters: éíáýžřčšěů
Thanks,
Tomas
Tagged:
«1

Answers

  • Tomeo
    Tomeo Member Posts: 303
    Any idea what could cause an above problem?
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Post the character sets and language settings for the operating system and Oracle database at both the sending and receiving locations.
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    Reading the docs is an option? http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_smtp.htm#i1001650

    "Text (VARCHAR2) data sent using WRITE_DATA is converted to US7ASCII before it is sent. If the text contains multibyte characters, each multibyte character in the text that cannot be converted to US7ASCII is replaced by a '?' character. If 8BITMIME extension is negotiated with the SMTP server using the EHLO subprogram, multibyte VARCHAR2 data can be sent by first converting the text to RAW using the UTL_RAW package, and then sending the RAW data using WRITE_RAW_DATA."
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    A good point ... are you defining your variables as VARCHAR2 or NVARCHAR2?
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    Perhaps you may try out utl_smtp.data instead of write_data.
  • Tomeo
    Tomeo Member Posts: 303
    Hi folks,

    please see below my settings:
    SQL> select * from nls_database_parameters
      2  ;
     
    PARAMETER                      VALUE
    ------------------------------ --------------------------------------------------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               AL32UTF8
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_RDBMS_VERSION              11.2.0.3.0
     
    20 rows selected
     
    SQL> select * from nls_instance_parameters;
     
    PARAMETER                                                                        VALUE
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    NLS_LANGUAGE                                                                     ENGLISH
    NLS_TERRITORY                                                                    CZECH REPUBLIC
    NLS_SORT                                                                         
    NLS_DATE_LANGUAGE                                                                
    NLS_DATE_FORMAT                                                                  
    NLS_CURRENCY                                                                     
    NLS_NUMERIC_CHARACTERS                                                           
    NLS_ISO_CURRENCY                                                                 
    NLS_CALENDAR                                                                     
    NLS_TIME_FORMAT                                                                  
    NLS_TIMESTAMP_FORMAT                                                             
    NLS_TIME_TZ_FORMAT                                                               
    NLS_TIMESTAMP_TZ_FORMAT                                                          
    NLS_DUAL_CURRENCY                                                                
    NLS_COMP                                                                         BINARY
    NLS_LENGTH_SEMANTICS                                                             BYTE
    NLS_NCHAR_CONV_EXCP                                                              FALSE
     
    17 rows selected
     
    SQL> select * from nls_session_parameters;
     
    PARAMETER                                                                        VALUE
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    NLS_LANGUAGE                                                                     ENGLISH
    NLS_TERRITORY                                                                    CZECH REPUBLIC
    NLS_CURRENCY                                                                     Kč
    NLS_ISO_CURRENCY                                                                 CZECH REPUBLIC
    NLS_NUMERIC_CHARACTERS                                                           ,.
    NLS_CALENDAR                                                                     GREGORIAN
    NLS_DATE_FORMAT                                                                  DD.MM.RR
    NLS_DATE_LANGUAGE                                                                ENGLISH
    NLS_SORT                                                                         BINARY
    NLS_TIME_FORMAT                                                                  HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT                                                             DD.MM.RR HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT                                                               HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT                                                          DD.MM.RR HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY                                                                Kč
    NLS_COMP                                                                         BINARY
    NLS_LENGTH_SEMANTICS                                                             BYTE
    NLS_NCHAR_CONV_EXCP                                                              FALSE
     
    17 rows selected
    I'm using a VARCHAR2 variable's type, never NVARCHAR.

    Thanks,
    Tomas
  • Tomeo
    Tomeo Member Posts: 303
    Using "utl_smtp.data" did not help, the same issue :(
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    Tomeo wrote:
    Using "utl_smtp.data" did not help, the same issue :(
    Unlikely that it will, as the special characters do not survive the cross network and cross platforms trips when send as plain text.

    RFC 5321 states:
    The mail data may contain any of the 128 ASCII characters.  All
    characters are to be delivered to the recipient's mailbox, including
    spaces, vertical and horizontal tabs, and other control characters.
    If the transmission channel provides an 8-bit byte (octet) data
    stream, the 7-bit ASCII codes are transmitted, right justified, in
    the octets, with the high-order bits cleared to zero.
    If you want to send special characters, the easiest IMO would probably be base64 encoding.
  • Tomeo
    Tomeo Member Posts: 303
    Hi Billy,

    I do not understand, what you mean by: "If you want to send special characters, the easiest IMO would probably be base64 encoding".
    How can I send a text in base64 encoding?

    Please send me an example, if any.

    Thanks,
    Tomas
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    7 bit ASCII is the only valid data content for an e-mail according to the SMTP RFC. So that is what you need to convert non 7 bit ASCII content (such as images and binaries) too.

    For images and binaries, I use base64 encoding.

    Never send special text characters (Unicode) via mail - in my case, the standard 128 ASCII characters suffices for the e-mail text message. So sorry, cannot provide examples of how to send Unicode text as I've never had to send such text.

    Your problem is however not an Oracle issue. It is a pure SMTP and MIME issues. Your code, crafting the e-mail as a MIME body and sending it via the SMTP protocol, needs to conform to the MIME and SMTP specifications. My suggestion is to use Google, dig up the relevant RFCs, and look for generic examples.

    Alternatively - create such an e-mail (with special/Unicode chars) in an e-mail reader, send it to your own mailbox, and then look at how the mail reader crafted that e-mail (via view raw e-mail option). If you use MS Outlook gunk, then I doubt that this will work as Outlook does not allow you the most fundamental of features. To view the actual e-mail (complete mail with headers) that was delivered to your mailbox. Use a proper mail reader like Thunderbird instead.
    Billy Verreynne
This discussion has been closed.