I'm trying to decode html containing html entities.
I've tried dbms_xmlgen.convert and utl_i18n.unescape_reference but the results are less than satisfactory.

   'dbms_xmlgen.convert' AS method,
   dbms_xmlgen.convert('\♥', 1) AS hearts,
   dbms_xmlgen.convert('\&',    1) AS amp_ent,
   dbms_xmlgen.convert('\&',  1) AS amp_dec,
   dbms_xmlgen.convert('\&', 1) AS amp_hex,
   dbms_xmlgen.convert('\激\光', 1) AS chinese_laser 
FROM dual
FROM dual;

The results I get are:

METHOD                        HEARTS      AMP_ENT     AMP_DEC     AMP_HEX     CHINESE_LASER
dbms_xmlgen.convert           ♥    &           &     &    激光
utl_i18n.unescape_reference   ¿           &           &           &           ¿¿

My actual problem involves Chinese characters, processed by a Java program to create PDF reports. I have no easy access to the Java code, but I do have control over the query used by the program.

An example of Chinese characters that I use for testing is 激光, which Google Translate tells me means 'laser' and which I receive encoded as 激光. These aren't decoded properly, as can be seen in the example above.

I realise that in the second row, the inverted question marks seem to indicate that the entities were converted, but can't be displayed properly. However, is this Oracle itself doing this, or the client (I tried in both SQL+ and Toad)? When I plug utl_i18n.unescape_reference into the query used by the Java program, it works for entities like ± (±), but again, not for the Chinese characters.

How can I have all entities properly decoded?

  • Should I use another function? (These were recommended somewhere on the Internet).
  • Should I change some settings? (Relevant settings shown below).

Relevant information

Oracle Database 11g Enterprise Edition Release - 64bit Production
SQL*Plus: Release

NLS_LANGUAGE                           AMERICAN
NLS_TERRITORY                          AMERICA
NLS_CHARACTERSET                       WE8MSWIN1252
  • ♥ isn't a standard XML entity (there's very few of those). Could you show an example with one of your Chinese chars, and please specify your locale settings.
    – Mat
    Nov 18, 2014 at 17:17

2 Answers 2


The chinese characters are decoded correctly with utl_i18n.unescape_reference. In fact they just aren't displayed properly in your query result which might not support those special characters.

You can confirm that with this SQL Fiddle.

It is the client which is respsonsible to display the characters appropriately. If the client can not display a character it may show an upside down question mark, something else or just plain garbage.

So by now, the real question is where do you want to display this string...

  • You're correct. I'm surprised the Java client can't handle the characters, so I'll have to look into what happens in there.
    – SQB
    Nov 30, 2014 at 11:32

You mention you have tried SQL*Plus and Toad; what is your client operating system, Windows? Unix? See NLS_OS_CHARSET Environment Variable that is an environment variable set on your client to a value that your client understands and supports. If you are on Unix, invoke the


command and see what LANG or LC_ALL is set to; you may need to set


or the like.

You can list the available locales in Unix with

locale -a
  • I'm on Windows 7.
    – SQB
    Nov 28, 2014 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.