Scrambling PII data in CLOB columns I am working on scrambling PII tags in CLOB columns such as &lt;FirstName&gt; or &lt;LastName&gt; or &lt;SSN_Number&gt;.

so I am using the below query to achieve the same.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();


The above query runs for really a long time(more than 6 hours) and at last fails with below error.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();
               xmltype (SUED_DATA),
               *
ERROR at line 4:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 3 (U+0003)
Error at line 181
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1 


I am not aware of what could be wrong with it. Is there anyway to optimize the same? What is that I am missing?
Looks like there's an error in your XML file (the character U+0003 is "ETX" - end of text, which is nonprinting and invalid in XML).  Run the XML file through a validator.

Also (and you probably know this), your atbash "scramble" is not particular secure.