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?


You can use the htf.escape_sc function:

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

Please escape &lt;this&gt; tag

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


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


function escape_sc(ctext in varchar2 character set any_cs)
         return varchar2 character set ctext%charset is
begin return(replace(
             replace(ctext, '&', '&' || 'amp;'),
                            '"', '&' || 'quot;'),
                            '<', '&' || 'lt;'),
                            '>', '&' || 'gt;'));

You can create this function yourself

But better use this variant of function dbms_xmlgen.convert

SQL> select dbms_xmlgen.convert('<test>&''"</test>') from dual
  2  /


Need Your Help

Test events with nunit

c# unit-testing events nunit c#-2.0

I'm just starting with TDD and could solve most of the problems I've faced on my own. But now I'm lost: How can I check if events are fired? I was looking for something like Assert.Raise or Assert....

How can I save variable values across program close/reopen?

delphi delphi-7

basically all I want to do is code my project so that it stores any saved variables for the next time the program is run. At the moment once I close and reopen the program it obviously resets any