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:
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="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:🇳🇴:P11_QUESTION_ID:2612348048</a>
<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>
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.
i.e. & = AND ,
' = APOS
An example of what needs to be achieved by the function should be:
Code:
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="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:🇳🇴:P11_QUESTION_ID:2612348048</a>
<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>
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.