HTML Entity decoding to Special characters

Question

I want to display special symbols in my output. For eg: My text may contain entity codes like <, &gt; etc. I want to display this as <, > in my output. I need to do this in SQL. I googled about this and got a function,

select dbms_xmlgen.convert('ABC <; ',0) from dual

This does the reverse process, it generates the output as 'ABC <'

I tried with decoding but it does not work. I even changed the sql command as, select dbms_xmlgen.convert('ABC <; ',1) from dual, where 1 is for entity_decode, but I don't get the desired output.

Answer 1

Instead of using DBMS_XMLGEN.convert, I used the function UTL_I18N.UNESCAPE_REFERENCE:

SELECT UTL_I18N.UNESCAPE_REFERENCE('ABC < ') FROM DUAL;

result:

ABC < 

More information on the Oracle doc: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_i18n.htm#i998992