Home » SQL & PL/SQL » SQL & PL/SQL » Scrambling the data for dev environment after refresh to hide/mask the sensitive data (oracle 11.2.0.4)
Scrambling the data for dev environment after refresh to hide/mask the sensitive data [message #674857] Tue, 19 February 2019 13:42 Go to next message
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;
/
Re: Scrambling the data for dev environment after refresh to hide/mask the sensitive data [message #674858 is a reply to message #674857] Tue, 19 February 2019 13:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You could just use REGEXP_REPLACE function something like:
update &TABLENAME T1 
set T1.xmlrecord = 
    regexp_replace(T1.xmlrecord,
                   '<c&FIELD_POSITION>.*</c&FIELD_POSITION>,
                   '<c&FIELD_POSITION>'||dbms_random.string('A',10) ||'</c&FIELD_POSITION>')
/
Previous Topic: Using Window Function in a query Block - Count and Sum
Next Topic: Retrun a limit set of rows
Goto Forum:
  


Current Time: Thu Mar 28 14:09:08 CDT 2024