Search code examples
sqlxmloraclescrambleupdatexml

Scrambling the data for dev environment after refresh to hide/mask the sensitive data

I am working on some scripts to scramble the data from specific columns. I am not very good in scripting but still some how managed to write the code with the help from stackflow Q&A
this table has xml column so I want to replace data from xml column by Field Position so sometimes it should be replaced by random varchar and sometimes with some other value from the same table , depending on the requirement.

Below is the table structure:

select * from USER.ACCOUNT;

ID                XML
-------------------- --------------------------------------------------
10043210281964       <row id="10043210281964" xml:space="preserve"><c1>
                     11365650</c1><c2>6970</c2><c3>

CAD1460801480110     <row id="CAD1460801480110" xml:space="preserve"><c
                     2>14600</c2><c3>AAAAAAAA/W

GBP1405608560123     <row id="GBP1405608560123" xml:space="preserve"><c
                     2>14056</c2><c3>AAAAAAAL<

10181005424866       <row id="10181005424866" xml:space="preserve"><c1>
                     588764</c1><c2>6970</c2><c3>AAAAA

10232000152850       <row id="10232000152850" xml:space="preserve"><c1>
                     23152850</c1><c2>6010</c2><c3>

10013200079509       <row id="10013200079509" xml:space="preserve"><c1>
                     890006</c1><c2>6970</c2><c3>AAAAA

10100618109100       <row id="10100618109100" xml:space="preserve"><c1>
                     11877032</c1><c2>6970</c2><c3>

10033200519959       <row id="10033200519959" xml:space="preserve"><c1>
                     11215154</c1><c2>6970</c2><c3>

10100614571766       <row id="10100614571766" xml:space="preserve"><c1>
                     181616</c1><c2>6304</c2><c3>AAAA

CAD1405606040116     <row id="CAD1405606040116" xml:space="preserve"><c
                     2>14056</c2><c3>AAAAAAAAAAA<

And the script I am trying to write not working properly somethings is missing ...

DECLARE
TABLENAME VARCHAR2(255);
FIELD_POSITION VARCHAR2(255);
BEGIN
update &TABLENAME T1 set T1.xmlrecord = updatexml(T1.xmlrecord,'/&TABLENAME/row/preserve[position()=&FIELD_POSITION]/text()', dbms_random.string('A',10) );
DBMS_OUTPUT.PUT_LINE(CHR(10)||'*******COLUMN UPDATED AS REQUESTED*******');
END;
/
Solution

  • You need to do an execute immediate if you want to do some DML using a dynamic table name in PL/SQL. Substitution strings (&TABLENAME) don't change that. 

    DECLARE
  TABLENAME VARCHAR2(255) := 'ACCOUNT';
  FIELD_POSITION VARCHAR2(255) := 'c2';
BEGIN
  execute immediate 'update ' || TABLENAME || ' T1 set T1.xmlrecord = updatexml(T1.xmlrecord,''/row/' || FIELD_POSITION || '/text()'', dbms_random.string(''A'',10) )';
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'*******COLUMN UPDATED AS REQUESTED*******');
END;
/

    EDIT: Oh, your Xpath query is wrong. It doesn't match anything, so updatexml doesn't replace anything in the xml. You just want /row/c2/text() instead of /ACCOUNT/row/preserve[position()=c2]/text(). See this for example:

    -- sample data
with t1 as (select xmltype('<row id="10043210281964" xml:space="preserve"><c1>11365650</c1><c2>6970</c2><c3>AAAAAAAA</c3></row>') as xmlrecord from dual)
select  
    updatexml(T1.xmlrecord,'/ACCOUNT/row/preserve[position()=c2]/text()', dbms_random.string('A',10) ) first_way,
    updatexml(T1.xmlrecord,'/row/c2/text()', dbms_random.string('A',10) ) second_way
from t1;

    I updated the pl/sql block above to have a working xpath query - try it again.