Adding additional SQL Scripts

Remember that we've created a additional table within the database for the random lookup? Sometimes you want to do some extra work. Creating lookup tables, combining translation tables, creating extra masking function. We can do that off course manually with some database tool, but DATPROF Privacy also offers features to integrate those custom scripts into your template.

We will add an additional script in the Script Manager that will create the synthetic lookup table. To open the Script Manager, click in the top menu bar Project → Additional scripts...

The script manager consists of different sections. On the left side you have the list of scripts. On the right side you see the content of the script.

Each script can be Enabled/Disabled from execution. When disabled, the script will not be executed.

You can choose if the script you've created is a SQL script or Operating System call.

In the dependency settings, you can specify when the script must be executed. There are 5 options:

Before disable process: this will execute the script at the complete beginning of the process
Before main process: this will execute the script after all constraints, triggers and indexes are bypassed, but before any of the configured masking functions are executed.
After main process: this will execute the script directly after all masking functions, but before the constraints, triggers and indexes are restored
After enable process: this will execute the script after the constraints, triggers and indexes are restored.
Edit dependencies: you can manually specify which functions or scripts are the predecessors or successors of this script.

We will add an script that will create or replace our lookup table from the template

Open the script manager by clicking in the upper menu on Project → Additional scripts...
Click on Add script to create a new script
Double click on the newly created script and change the name to CRE_LOOKUP

For the script content you can copy paste / or write the following PL/SQL script. This will drop the lookup table if it already exists and than creates it.

SQL

declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('DPF_ORGANIZATIONS');
   if c = 1 then
      execute immediate 'drop table DPF_ORGANIZATIONS';
   end if;
   execute immediate q'[create table DPF_ORGANIZATIONS (value VARCHAR(200))]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Akorn')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Allegheny Technologies')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('AutoNation')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Bed Bath Beyond')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Boyd Gaming')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Commercial Metals Company')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('CNO Financial Group')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Chesapeake Energy')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Charles River Laboratories')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Mercury General Corp')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('Maximus Inc')]';
   execute immediate q'[insert into  DPF_ORGANIZATIONS values ('NetScout Systems')]';
   
end;

Make sure the Script type is set to SQL
Click on Edit dependencies and add a successor dependency to the Random Lookup on SUPPLIER.NAME (The script must run first!)
Click OK in the dependency editor and Close in the Script manager.

If you got stuck in adding the script, this short clip will show you how to use the script manager.