Scrambling the data for dev environment after refresh to hide/mask the sensitive data [message #674857] |
Tue, 19 February 2019 13:42 |
|
vancouver_dba
Messages: 1 Registered: February 2019
|
Junior Member |
|
|
Hi Guys ,
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.
Need your help guys
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;
/
|
|
|
|