Replace Illegal XML characters with value from table Oracle PL\SQL


Staff member
I have a requirement to check all text fields in a Database schema for any Illegal XML characters and replace them with a predefined set of acceptable values. This is to form part of a Data transformation rule, than can be called from other functions. So this function could be asked to called over a billion times on our dataset, so I need it to operate really efficiently.

i.e. & = AND ,
' = APOS

An example of what needs to be achieved by the function should be:

Update sometable set somefield = functioncall('f&re'd');

should result in
somefield having the value of ' fANDreAPOSd'

This is to carried out by a generic type PL/SQL function that takes an input of a text field and iterate through that field and replace all illegal values.

I have had a look at <a href="" rel="nofollow">🇳🇴:P11_QUESTION_ID:2612348048</a>

<a href="" rel="nofollow"></a>

For some ideas, but I have my concerns over efficiency and the flexibility of these soltuions.

The way the client wants to handle the solution is to have a table configured to contain an illegal character and it's prefered replacement. The function then uses the values selected from this table to preform the replacements.