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

Question

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('<', '<', string) manually all the special characters?

score 39 · Accepted Answer · answered Jun 16 '10 at 13:39

39

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

answered Jun 16 '10 at 13:39

Tony Andrews

129,880
21
220
259

score 10 · Answer 2 · answered Jun 06 '16 at 17:36

10

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

answered Jun 06 '16 at 17:36

Tim Funk

869
7
11

Also note that this function returns a CLOB (per details in link provided). – Tim Newton Jul 01 '19 at 17:13
No, it returns a CLOB if you pass a CLOB, or a VARCHAR2 if you pass a VARCHAR2. This example returns a VARCHAR2. – kfinity Feb 10 '20 at 16:27

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

2 Answers2

Linked