PL / SQL conversion of special characters

Question

I am creating a simple XML 1.0 file using a short PL / SQL function populated with data from a table.

Data from the table also contains HTML characters, such as <>, etc. For these special characters, I built a short search and replace function that looks like this:

newXmlString := REPLACE(xmlString, '&', '&amp;' ); newXmlString := REPLACE(newXmlString, '\', '' ); newXmlString := REPLACE(newXmlString, '<', '&lt;' ); newXmlString := REPLACE(newXmlString, '>', '&gt;' ); newXmlString := REPLACE(newXmlString, '"', '&quot;' ); newXmlString := REPLACE(newXmlString, '''', '&apos;' ); 

Now the table has more data, which is why the XML file cannot be checked due to special control characters ( https://en.wikipedia.org/wiki/Control_character ) like:

• ETX (End of text)
• SYN (synchronous idle)

Note. Not every control character damages XML file validation! Linear transitions or carriage returns are still possible.

Of course, now I can search for and replace them, for example:

 newXmlString := REPLACE(newXmlString, chr(3), '' ); -- ETX end of text 

But is there a built-in function or something like a library that I can use with PL / SQL without listing and finding and replacing them?

UPDATE 1

I also tried using the dbms_xmlgen.getxml function, but this function throws an error message due to the unsuccessful conversion of w980> to escaped char. '+

UPDATE 2

I tried using REGEXP_REPLACE(STRING_VALUE,'[[:cntrl:]]') , which will work, but it will also remove the line breaks that we want to keep, and also does not affect the validation of the XML file.

Answer 1

TRANSLATE is really the way to go. Create a string using the CHR function and apply it only once. Here is an example for ETX: 3, EOT: 4 and SYN: 22. If necessary, you can add others.

Note the "a" at the beginning of the line, which is returned as the only character in the second line. This function needs one set that is not eliminated.

 FUNCTION clean_control( in_text IN VARCHAR2 ) RETURN VARCHAR2 IS v_search VARCHAR2( 30 ) := 'a' || CHR( 3 ) || CHR( 4 ) || CHR( 22 ); BEGIN RETURN TRANSLATE( in_text, v_search, 'a' ); END;