title: Replace Illegal XML characters with value from table Oracle PL\SQL
body: <p>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.</p>
  
  <p>i.e.  &amp;  =  AND , 
  ' = APOS</p>
  
  <p>An example of what needs to be achieved by the function should be:</p>
  
  <p><code>Update sometable set somefield = functioncall('f&amp;re'd');</code></p>
  
  <p>should result in 
  somefield having the value of ' fANDreAPOSd'</p>
  
  <p>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.</p>
  
  <p>I have had a look at <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2612348048" rel="nofollow">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2612348048</a></p>
  
  <p><a href="http://decipherinfosys.wordpress.com/2007/11/27/removing-un-wanted-text-from-strings-in-oracle/" rel="nofollow">http://decipherinfosys.wordpress.com/2007/11/27/removing-un-wanted-text-from-strings-in-oracle/</a></p>
  
  <p>For some ideas, but I have my concerns over efficiency and the flexibility of these soltuions.</p>
  
  <p>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.</p>