An Insertion or Substitution rule will use information from the specified Data Set to modify the contents of the specified table and column.
Often, the generated replacement data is not quite in the required format - it needs further processing to be placed in an acceptable form. For example, assume that a substitution was required on the EMPID column in order to "mask" it for security reasons. The format of the EMPID data is IDnnnnnn - in other words, the two characters "ID" followed by a 6 digit integer. No Data Sets provided by the Data Masker could generate data in such a site specific format.
That is not to say the Data Masker does not provide a solution to the above problem. All that is required is to use a Data Set close to the required format and to create a User Defined Procedure which will modify the incoming substitution data as required. Here's how it could be done:
Two columns in the temporary table will contain data relevant to the Substitution rule: RID1 and UTIL1. The RID1 column will hold the ROWID to be substituted and the UTIL1 column will hold the data to be substituted. For example:
SQL> SELECT RID1, UTIL1 from DM_ROWID_TEMP0; RID1 UTIL1 ------------------ ------------------ AABCjDAAnAAAHd7AAA 100865 AABCjDAAnAAAHd7AAB 100866 AABCjDAAnAAAHd7AAC 100867 AABCjDAAnAAAHd7AAD 100868
Typically the substitution is carried out by the Data Masker in a anonymous PL/SQL block using a statement like:
UPDATE EMP set EMPID=<UTIL1> where ROWID=<RID1>;
The anonymous PL/SQL block sits in a loop and reads each line of the temporary table and substitutes in the appropriate RID1 and UTIL1 values. Custom data modifications can easily be made by writing your own procedure and changing the UPDATE statement to suit the requirements of the data format. In the example above the statement would probably be modified to something like:
UPDATE EMP set EMPID='ID'||<UTIL1> where ROWID=<RID1>;
which concatenates the string 'ID' onto the value in the UTIL1 column prior to insertion.
create or replace PROCEDURE my_EMPID_proc(RESERVED1 INTEGER, RESERVED2 VARCHAR2) IS
BEGIN
DECLARE
CURSOR c1 is
-- note: we always reference DM_ROWID_TEMP0
select rid1, util1 from DM_ROWID_TEMP0;
rid1 rowid;
util1 varchar2(4000);
BEGIN
open c1;
LOOP
FETCH c1 into rid1,util1;
EXIT when c1%NOTFOUND;
-- note the reference to EMP and EMPID. UserDef Procs
-- are always coded for a specific table and column
UPDATE EMP A set EMPID = 'ID'||util1 where A.rowid=rid1;
END LOOP;
close c1;
END;
END my_EMPID_proc;
/
Important Note: User Defined Procedures are always specific to the table and column they reference.
Also note that there is no Commit; statement in the above code. The Data Masker will automatically send commits after the conclusion of the procedure.
The above example of a User Defined Procedure is specific to a Substitution rule. A User Defined Procedure for an Insertion rule is identical except that the RID1 column is not used (there are no rowids for inserts) and the UPDATE statement is re-written as an INSERT statement using only the UTIL1 column.
