Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to escape <, >, and & characters to html entities in Oracle PL/SQL

I need to send HTML emails directly from oracle PL/SQL package. This works almost fine.

I have problem with the fact that some of the data fetched from a table contain things like <S>, <L>, and similar fragments, which sometimes ar treated as HTML tags, and even if not, they are always ignored and never displayed.

So, I need to escape this column before inserting into email body.

Is there a function to escape html special chars into entities automaticly? Or do I need to replace('<', '&lt;', string) manually all the special characters?

like image 343
SWilk Avatar asked Jun 16 '10 12:06

SWilk


2 Answers

You can use the htf.escape_sc function:

SQL> select htf.escape_sc('Please escape <this> tag') from dual;

HTF.ESCAPE_SC('PLEASEESCAPE<THIS>TAG')
------------------------------------------------------------------
Please escape &lt;this&gt; tag
like image 106
Tony Andrews Avatar answered Sep 20 '22 17:09

Tony Andrews


Also available is DBMS_XMLGEN.CONVERT which can handle a clob.

Example:

select DBMS_XMLGEN.CONVERT('<foo>') from dual

Details: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm

like image 28
Tim Funk Avatar answered Sep 20 '22 17:09

Tim Funk