Escape XML special characters in strings obtained by querying oracle DB column values

Question

Suppose my DB column which is of datatype varchar2 has values that containcharacters like '<' '>' and '&' and i want them to be escaped like & lt; , & gt; etc when obtained using select query on these columns , is there some built-in feature in oracle , jdbc to perform this activity ?

Answer 1

You can use the dbms_xmlgen.convert() function:

select dbms_xmlgen.convert('<element>Text with "quotes" and ''&''</element>')
from dual;

DBMS_XMLGEN.CONVERT('<ELEMENT>TEXTWITH"QUOTES"AND''&''</ELEMENT>')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------------------------------------
&lt;element&gt;Text with &quot;quotes&quot; and &apos;&amp;&apos;&lt;/element&gt;

There are overloaded versions for varchar2 and clob values. Just use your table column instead of the fixed string. Or convert to an XMLType variable which wil encode automatically, or use another XMLGen function if that's appropriate for your data and required output.

Answer 2

htf.escape_sc() function do what you expect. But I would rather prefer doing this with Java itself.

SQL> select htf.escape_sc('<Welcome>Hello WOrld</Welcome>') parsed FROM DUAL;

PARSED
--------------------------------------------------------------------------------
&lt;Welcome&gt;Hello WOrld&lt;/Welcome&gt;

Biggest Limitation is.. It is restricted to Maximum of 4000 charcters(after parsing) for VARCHAR column.. And it threw an exception if it exceeds!! So, Please have these parsing in Client side.

More on this from Docs