CODEWITHSUNDEEP
oracle11gcharacter-encodingxmltype
Leo Lozes
Leo Lozes

Reputation: 1388

Keep UTF8 encoding when generating a new XMLTYPE in Oracle

I have a cursor that retrieves text from a table. I then have a FOR .. LOOP that iterates on that cursor, and creates an XMLTYPE, like this:

  TRANSLATION := XMLTYPE('<I18NElement>
                          <i18nElementType>'||ELEMENTTYPE||'</i18nElementType>
                          <key>'||REC.CODE||'</key>
                          <languageCode>'||REC.CODIDI||'</languageCode>
                          <i18nField>
                            <fieldName>commercialName</fieldName>
                          </i18nField>
                          <i18nField>
                            <fieldName>description</fieldName>
                            <translation><![CDATA['||REC.DESCRIPTION||']]></translation>
                          </i18nField>
                          <isDefault>'||BOOLEANDEFAULT||'</isDefault>
                        </I18NElement>');

The UTF-8 encoded text is in the REC.DESCRIPTION field. If I insert this text directly in another table, the encoding is kept correctly. However, when I insert the XMLTYPE in another table, the encoding is lost:

<I18NElement>
  <i18nElementType>Hotel</i18nElementType>
  <key>GUESTI@1036@Hotel@GUESTI#1036</key>
  <languageCode>ES</languageCode>
  <i18nField>
    <fieldName>commercialName</fieldName>
  </i18nField>
  <i18nField>
    <fieldName>description</fieldName>
    <translation><![CDATA[Hotel de playa de 3 estrellas, de gestión familiar desde 1964. Larga experiéncia el en trato familiar con el cliente. Situado en Malgrat de Mar, Costa Barcelona-Maresme, a 80 Km del aeropuerto de Barcelona y a 40 Km del de Girona.

Este hotel, está compuesto por un edificio principal de cuatro plantas y otro anexo de dos, contiene un total de 227 habitaciones. Está situado a solamente 50 metros de la playa y del paseo marítimo y el centro del pueblo está a una distancia de 400 metros.]]></translation>
  </i18nField>
  <isDefault>true</isDefault>
</I18NElement>

Here's the original text:

Hotel de playa de 3 estrellas, de gestión familiar desde 1964. Larga experiéncia el en trato familiar con el cliente. Situado en Malgrat de Mar, Costa Barcelona-Maresme, a 80 Km del aeropuerto de Barcelona y a 40 Km del de Girona.

Este hotel, está compuesto por un edificio principal de cuatro plantas y otro anexo de dos, contiene un total de 227 habitaciones. Está situado a solamente 50 metros de la playa y del paseo marítimo y el centro del pueblo está a una distancia de 400 metros.

I've tried all sort of combinations with UTL_I18N utilities, nothing is working so far. The database NLS_CHARACTERSET is set to AL32UTF8.

Any information you can provide me would be greatly appreciated! I found a lot of related questions in stackoverflow, but none worked for me so far ...

Upvotes: 0

Views: 792

Answers (1)

Leo Lozes
Leo Lozes

Reputation: 1388

Turns out it was the Toad (12.1) XMLTYPE pop-up editor that doesn't show the text correctly encoded for some reason. The data is correctly inserted, using SQL Developer there's no encoding problem.

Edit: Somehow, it looks like it depends on software versions and OS too. We have a machine where the encoding looks fine with Toad 9.7 (I have the 12.1), another one with SQL Developer 4.2 and Ubuntu where it's fine too, and another one where none of those shows the right encoding (here a screenshot with both SQL Developer and Toad):

enter image description here

Luckily it seems that the database is fine, since our UTF-8 Java application shows the right encoding.

There have been a significant amount of hours thrown to the bin though.

Upvotes: 2

Related Questions