一、简介
首先简单介绍下Oracle Data Masking的作用，我对它的理解是通过一系列的我们自己定规则（规则组合可以千变万化）来替换或混淆原始数据，从而达到保护生产数据的目的。
刚开始我以为只有在grid control 里面才有Data Masking这个功能，后来发现Oracle Enterprise Manager Database Control 11g Release 2和Oracle Enterprise Manager Grid Control 10.2.0.4，Oracle Enterprise Manager Grid Control 10.2.0.5上面都有这个功能，不过Oracle Enterprise Manager Grid Control 10.2.0.1上面是没有这个小功能的，至少要升级到Oracle Enterprise Manager Grid Control 10.2.0.4才行。
二、具体使用方法：
2.1进入EM界面：
2.2选择Schema选项
2.3选择Data Masking选项下面的Definitions
2.4创建Masking规则，看见下面有个Format Library(格式库) ，也可以自己创建格式，Masking规则就是调用这个格式。
2.5一个创建好的Data Masking
2.6 点击Edit就可以添加要保护的表中的列，比如说工资
2.6.1呈现的界面是这样的
2.6.2选择用户和表点击search就出现一下界面，可以选择要保护的数据
2.6.3选择列点击define format and add
2.6.4出现下面的界面，在这里可以直接导入现有的格式，也可以选择格式的类型，也可以添加条件
2.7点击HR Employee Mask
可以看到我要保护的一些敏感列（实验中选取的是hr_test用户下面的employees表作为研究对象），红色方框内是要保护的一些敏感数据（包括员工号，薪水，名字等等）这些都是自己选择的
2.8下面这个菜单栏就可以完成一系列任务，包括用Data Masking克隆一个测试库，这样我们的生产数据就得到保障了
2.9可以创建脚本看下它的实现过程
2.10脚本内容：
set feedback off
set serveroutput on
set pagesize 0
set ver off
set echo off
set timing off
spool /oracle/product/11.2.0/db_1/dbs/masking41.log
-- Script Header Section
-- ==============================================
-- functions and procedures
CREATE OR REPLACE PROCEDURE mgmt$mask_sendMsg (msg IN VARCHAR2) IS
msg1 VARCHAR2(1020);
len INTEGER := length(msg);
i INTEGER := 1;
BEGIN
dbms_output.enable (1000000);
LOOP
msg1 := SUBSTR (msg, i, 255);
dbms_output.put_line (msg1);
len := len - 255;
i := i + 255;
EXIT WHEN len <= 0;
END LOOP;
END mgmt$mask_sendMsg;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_errorExit (msg IN VARCHAR2) IS
BEGIN
mgmt$mask_sendMsg (msg);
mgmt$mask_sendMsg ('errorExit'||'!');
END mgmt$mask_errorExit;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_errorExitOraError (msg IN VARCHAR2, errMsg IN VARCHAR2) IS
BEGIN
mgmt$mask_sendMsg (msg);
mgmt$mask_sendMsg (errMsg);
mgmt$mask_sendMsg ('errorExitOraError'||'!');
END mgmt$mask_errorExitOraError;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_checkDBAPrivs
AUTHID CURRENT_USER IS
granted_role REAL := 0;
user_name user_users.username%type;
BEGIN
SELECT USERNAME INTO user_name FROM USER_USERS;
EXECUTE IMMEDIATE 'SELECT 1 FROM SYS.DBA_ROLE_PRIVS WHERE GRANTED_ROLE = ''DBA'' AND GRANTEE = :1'
INTO granted_role USING user_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -01403 OR SQLCODE = -00942 THEN
mgmt$mask_sendMsg ( 'WARNING checking privileges... User Name: ' || user_name);
mgmt$mask_sendMsg ( 'User does not have DBA privs. ' );
mgmt$mask_errorExitOraError ( 'The script will fail if it tries to perform operations for which the user lacks the appropriate privilege. ',' ' );
END IF;
END mgmt$mask_checkDBAPrivs;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_setUpJobTable (script_id IN INTEGER, job_table IN VARCHAR2, step_num OUT INTEGER)
AUTHID CURRENT_USER IS
ctsql_text VARCHAR2(200) := 'CREATE TABLE ' || job_table || '(SCRIPT_ID NUMBER, LAST_STEP NUMBER, unique (SCRIPT_ID))';
itsql_text VARCHAR2(200) := 'INSERT INTO ' || job_table || ' (SCRIPT_ID, LAST_STEP) values (:1, :2)';
stsql_text VARCHAR2(200) := 'SELECT last_step FROM ' || job_table || ' WHERE script_id = :1';
TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
stsql_cur CurTyp; -- declare cursor variable
BEGIN
step_num := 0;
BEGIN
EXECUTE IMMEDIATE ctsql_text;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
OPEN stsql_cur FOR -- open cursor variable
stsql_text USING script_id;
FETCH stsql_cur INTO step_num;
IF stsql_cur%FOUND THEN
NULL;
ELSE
EXECUTE IMMEDIATE itsql_text USING script_id, step_num;
COMMIT;
step_num := 1;
END IF;
CLOSE stsql_cur;
EXCEPTION
WHEN OTHERS THEN
mgmt$mask_errorExit ('ERROR selecting or inserting from table: ' || job_table);
return;
END;
return;
EXCEPTION
WHEN OTHERS THEN
mgmt$mask_errorExit ('ERROR accessing table: ' || job_table);
return;
END mgmt$mask_setUpJobTable;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_deleteJobTableEntry(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER, highest_step IN INTEGER)
AUTHID CURRENT_USER IS
delete_text VARCHAR2(200) := 'DELETE FROM ' || job_table || ' WHERE SCRIPT_ID = :1';
BEGIN
IF step_num <= highest_step THEN
return;
END IF;
BEGIN
EXECUTE IMMEDIATE delete_text USING script_id;
IF SQL%NOTFOUND THEN
mgmt$mask_errorExit ('ERROR deleting entry from table: ' || job_table);
return;
END IF;
EXCEPTION
WHEN OTHERS THEN
mgmt$mask_errorExit ('ERROR deleting entry from table: ' || job_table);
return;
END;
COMMIT;
END mgmt$mask_deleteJobTableEntry;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_setStep (script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER)
AUTHID CURRENT_USER IS
update_text VARCHAR2(200) := 'UPDATE ' || job_table || ' SET last_step = :1 WHERE script_id = :2';
BEGIN
-- update job table
EXECUTE IMMEDIATE update_text USING step_num, script_id;
IF SQL%NOTFOUND THEN
mgmt$mask_sendMsg ('NOTFOUND EXCEPTION of sql_text: ' || update_text);
mgmt$mask_errorExit ('ERROR accessing table: ' || job_table);
return;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
mgmt$mask_errorExit ('ERROR accessing table: ' || job_table);
return;
END mgmt$mask_setStep;
/
CREATE OR REPLACE FUNCTION mgmt$mask_randomencode (i_input VARCHAR2, pad_length NUMBER) RETURN VARCHAR2
AUTHID CURRENT_USER IS
TYPE charmap IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
l_input NUMBER;
l_mod NUMBER;
l_retCode VARCHAR2(100);
l_map charmap;
l_base number := 25;
BEGIN
if (i_input is null) then
return lpad('a',pad_length,'a');
end if;
l_map(0) := 'a';
l_map(1) := 'b';
l_map(2) := 'c';
l_map(3) := 'd';
l_map(4) := 'e';
l_map(5) := 'f';
l_map(6) := 'g';
l_map(7) := 'h';
l_map(8) := 'i';
l_map(9) := 'j';
l_map(10) := 'k';
l_map(11) := 'l';
l_map(12) := 'm';
l_map(13) := 'n';
l_map(14) := 'o';
l_map(15) := 'p';
l_map(16) := 'q';
l_map(17) := 'r';
l_map(18) := 's';
l_map(19) := 't';
l_map(20) := 'u';
l_map(21) := 'v';
l_map(22) := 'w';
l_map(23) := 'x';
l_map(24) := 'y';
l_map(25) := 'z';
l_input := i_input;
l_retCode := '';
LOOP
-- skip 'a' for padding
l_mod := l_input mod l_base + 1;
l_retCode := l_map(l_mod) || l_retCode ;
IF (l_input >= l_base) THEN
l_input := round(l_input / l_base);
ELSE
l_input := 0;
END IF;
-- dbms_output.put_line('left ' || l_input || ' mod ' || l_mod );
EXIT WHEN l_input = 0;
END LOOP;
return lpad(l_retCode, pad_length, 'a');
END ;
/
create or replace type mgmt$mask_array_list as table of varchar2(128)
/
CREATE OR REPLACE PROCEDURE mgmt$mask_setUpMappingTable (sid VARCHAR2, sourcecol VARCHAR2, sourcetab VARCHAR2)
AUTHID CURRENT_USER IS
ctsql_text VARCHAR2(400) := 'CREATE TABLE MGMT_DM_TT_' || sid ||
'(ORIG_VAL, NEW_VAL) ' ||
' AS (SELECT '|| sourcecol || ',' || sourcecol ||
' FROM ' || sourcetab || ' WHERE ROWNUM<1) ';
itsql_text VARCHAR2(400) := 'INSERT INTO MGMT_DM_TT_' || sid ||
' SELECT ' || sourcecol || ', dm_seq.nextval FROM ' ||
'( SELECT DISTINCT ' || sourcecol || ' FROM ' || sourcetab || ')';
dlsql_text VARCHAR2(400) := 'DELETE FROM MGMT_DM_TT_' || sid ;
BEGIN
BEGIN
EXECUTE IMMEDIATE ctsql_text;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE dlsql_text;
END;
EXCEPTION
WHEN OTHERS THEN
mgmt$mask_errorExit ('ERROR accessing table: MGMT_DM_TT_' || sid);
END mgmt$mask_setUpMappingTable;
/
CREATE OR REPLACE PROCEDURE mgmt$step_1_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 1 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMIT');
EXECUTE IMMEDIATE 'COMMIT';
mgmt$mask_sendMsg ('ALTER SESSION ENABLE PARALLEL DML');
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_1_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_2_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 2 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_46" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_46" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_2_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_3_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 3 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "NATIONAL_ID" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
execute immediate ''create table MGMT_DM_TT_46
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(100)) orig_val, CAST(null AS VARCHAR2(100)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(DBSNMP.DM_FMTLIB.MGMT_DM_GEN_SSN_FH(rid, ''''NATIONAL_ID'''',
lpad(20000000 + mod(lpad(trunc(dbms_random.value(0, substr(718999999, 1, 6 - least(6, greatest(''||adj||'' - 0, 0))))), 6 - least(6, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(6, greatest(''||adj||'' - 0, 0))),0), 3 + least(6, greatest(''||adj||'' - 0, 0)), 0), 718999999), 9, 0)
) AS VARCHAR2(100)) when s.subset = 2 then
CAST(DBSNMP.DM_FMTLIB.MGMT_DM_GEN_UK_NIN_FH(rid, ''''NATIONAL_ID'''',
a2.new_val
||a3.new_val
||lpad(trunc(dbms_random.value(0, power(10, 3 - least(3, greatest(''||adj||'' - 0, 0))) - 1)), 3 - least(3, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(3, greatest(''||adj||'' - 0, 0))),0),3 + least(3, greatest(''||adj||'' - 0, 0)), 0)
||a5.new_val
) AS VARCHAR2(100)) when s.subset = 3 then
CAST(
orig_val
AS VARCHAR2(100))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "NATIONAL_ID" orig_val, min(rowid) rid, min(
case
when national_id in (select national_id from hr_test.employees where country_id = ''''UK'''' ) then 1
when national_id in (select national_id from hr_test.employees where country_id = ''''UK'''' ) then 2
when 1=1 then 3
end
) subset
from "HR_TEST"."EMPLOYEES" group by "NATIONAL_ID")) s
,(select rownum rn, column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''E'''',''''G'''',''''H'''',''''J'''',''''K'''',''''L'''',''''M'''',''''N'''',''''O'''',''''P'''',''''R'''',''''S'''',''''T'''',''''W'''',''''X'''',''''Y'''',''''Z'''') as mgmt$mask_array_list)) order by dbms_random.value) a2
,(select rownum rn,'||
' column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''E'''',''''G'''',''''H'''',''''J'''',''''K'''',''''L'''',''''M'''',''''N'''',''''O'''',''''P'''',''''R'''',''''S'''',''''T'''',''''W'''',''''X'''',''''Y'''',''''Z'''') as mgmt$mask_array_list)) order by dbms_random.value) a3
,(select rownum rn, column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''D'''') as mgmt$mask_array_list)) order by dbms_random.value) a5
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, 20)+1 = a2.rn
and mod(s.rn - 1, 20)+1 = a3.rn
and mod(s.rn - 1, 4)+1 = a5.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_46"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "NATIONAL_ID" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
execute immediate ''create table MGMT_DM_TT_46
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(100)) orig_val, CAST(null AS VARCHAR2(100)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(DBSNMP.DM_FMTLIB.MGMT_DM_GEN_SSN_FH(rid, ''''NATIONAL_ID'''',
lpad(20000000 + mod(lpad(trunc(dbms_random.value(0, substr(718999999, 1, 6 - least(6, greatest(''||adj||'' - 0, 0))))), 6 - least(6, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(6, greatest(''||adj||'' - 0, 0))),0), 3 + least(6, greatest(''||adj||'' - 0, 0)), 0), 718999999), 9, 0)
) AS VARCHAR2(100)) when s.subset = 2 then
CAST(DBSNMP.DM_FMTLIB.MGMT_DM_GEN_UK_NIN_FH(rid, ''''NATIONAL_ID'''',
a2.new_val
||a3.new_val
||lpad(trunc(dbms_random.value(0, power(10, 3 - least(3, greatest(''||adj||'' - 0, 0))) - 1)), 3 - least(3, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(3, greatest(''||adj||'' - 0, 0))),0),3 + least(3, greatest(''||adj||'' - 0, 0)), 0)
||a5.new_val
) AS VARCHAR2(100)) when s.subset = 3 then
CAST(
orig_val
AS VARCHAR2(100))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "NATIONAL_ID" orig_val, min(rowid) rid, min(
case
when national_id in (select national_id from hr_test.employees where country_id = ''''UK'''' ) then 1
when national_id in (select national_id from hr_test.employees where country_id = ''''UK'''' ) then 2
when 1=1 then 3
end
) subset
from "HR_TEST"."EMPLOYEES" group by "NATIONAL_ID")) s
,(select rownum rn, column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''E'''',''''G'''',''''H'''',''''J'''',''''K'''',''''L'''',''''M'''',''''N'''',''''O'''',''''P'''',''''R'''',''''S'''',''''T'''',''''W'''',''''X'''',''''Y'''',''''Z'''') as mgmt$mask_array_list)) order by dbms_random.value) a2
,(select rownum rn,'||
' column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''E'''',''''G'''',''''H'''',''''J'''',''''K'''',''''L'''',''''M'''',''''N'''',''''O'''',''''P'''',''''R'''',''''S'''',''''T'''',''''W'''',''''X'''',''''Y'''',''''Z'''') as mgmt$mask_array_list)) order by dbms_random.value) a3
,(select rownum rn, column_value new_val from table(cast(mgmt$mask_array_list(''''A'''',''''B'''',''''C'''',''''D'''') as mgmt$mask_array_list)) order by dbms_random.value) a5
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, 20)+1 = a2.rn
and mod(s.rn - 1, 20)+1 = a3.rn
and mod(s.rn - 1, 4)+1 = a5.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_46"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_3_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_4_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 4 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_47" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_47" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_4_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_5_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 5 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
cnt0 NUMBER;
hcnt0 NUMBER;
begin
select length(count(*)) into adj from (select distinct "FIRST_NAME" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 2, 0);
select count(*) into cnt0 from (select distinct "FIRST_NAME" from "HR_TEST"."MASK_DATA");
hcnt0 := greatest(0, cnt0-1);
hcnt0 := least(4294967295, hcnt0);
execute immediate ''create table MGMT_DM_TT_47
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(20)) orig_val, CAST(null AS VARCHAR2(20)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS VARCHAR2(20))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "FIRST_NAME" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "FIRST_NAME")) s
,(select rownum rn, "FIRST_NAME" new_val from "HR_TEST"."MASK_DATA" order by dbms_random.value) a0
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, '' || cnt0 || '')+1 = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_47"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
cnt0 NUMBER;
hcnt0 NUMBER;
begin
select length(count(*)) into adj from (select distinct "FIRST_NAME" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 2, 0);
select count(*) into cnt0 from (select distinct "FIRST_NAME" from "HR_TEST"."MASK_DATA");
hcnt0 := greatest(0, cnt0-1);
hcnt0 := least(4294967295, hcnt0);
execute immediate ''create table MGMT_DM_TT_47
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(20)) orig_val, CAST(null AS VARCHAR2(20)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS VARCHAR2(20))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "FIRST_NAME" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "FIRST_NAME")) s
,(select rownum rn, "FIRST_NAME" new_val from "HR_TEST"."MASK_DATA" order by dbms_random.value) a0
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, '' || cnt0 || '')+1 = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_47"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_5_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_6_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 6 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_48" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_48" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_6_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_7_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 7 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "SALARY" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 2, 0);
execute immediate ''create table MGMT_DM_TT_48
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(8, 2)) orig_val, CAST(null AS NUMBER(8, 2)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS NUMBER(8, 2))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "SALARY" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "SALARY")) s
,(select rownum rn, new_val from (select distinct "SALARY" new_val from "HR_TEST"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0
where 1=1 and s.orig_val is not null
and s.rn = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_48"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "SALARY" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 2, 0);
execute immediate ''create table MGMT_DM_TT_48
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(8, 2)) orig_val, CAST(null AS NUMBER(8, 2)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS NUMBER(8, 2))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "SALARY" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "SALARY")) s
,(select rownum rn, new_val from (select distinct "SALARY" new_val from "HR_TEST"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0
where 1=1 and s.orig_val is not null
and s.rn = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_48"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_7_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_8_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 8 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_49" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_49" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_8_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_9_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 9 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
cnt0 NUMBER;
hcnt0 NUMBER;
begin
select length(count(*)) into adj from (select distinct "LAST_NAME" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
select count(*) into cnt0 from (select distinct "LAST_NAME" from "HR_TEST"."MASK_DATA");
hcnt0 := greatest(0, cnt0-1);
hcnt0 := least(4294967295, hcnt0);
execute immediate ''create table MGMT_DM_TT_49
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS VARCHAR2(25))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "LAST_NAME" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "LAST_NAME")) s
,(select rownum rn, "LAST_NAME" new_val from "HR_TEST"."MASK_DATA" order by dbms_random.value) a0
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, '' || cnt0 || '')+1 = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_49"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
cnt0 NUMBER;
hcnt0 NUMBER;
begin
select length(count(*)) into adj from (select distinct "LAST_NAME" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
select count(*) into cnt0 from (select distinct "LAST_NAME" from "HR_TEST"."MASK_DATA");
hcnt0 := greatest(0, cnt0-1);
hcnt0 := least(4294967295, hcnt0);
execute immediate ''create table MGMT_DM_TT_49
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS VARCHAR2(25))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "LAST_NAME" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "LAST_NAME")) s
,(select rownum rn, "LAST_NAME" new_val from "HR_TEST"."MASK_DATA" order by dbms_random.value) a0
where 1=1 and s.orig_val is not null
and mod(s.rn - 1, '' || cnt0 || '')+1 = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_49"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_9_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_10_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 10 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_50" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_50" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_10_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_11_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 11 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "EMPLOYEE_ID" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
execute immediate ''create table MGMT_DM_TT_50
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(6)) orig_val, CAST(null AS NUMBER(6)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
lpad(100000 + mod(lpad(trunc(dbms_random.value(0, substr(899999, 1, 3 - least(3, greatest(''||adj||'' - 0, 0))))), 3 - least(3, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(3, greatest(''||adj||'' - 0, 0))),0), 3 + least(3, greatest(''||adj||'' - 0, 0)), 0), 899999), 6, 0)
AS NUMBER(6))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "EMPLOYEE_ID" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "EMPLOYEE_ID")) s
where 1=1 and s.orig_val is not null
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_50"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "EMPLOYEE_ID" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
execute immediate ''create table MGMT_DM_TT_50
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(6)) orig_val, CAST(null AS NUMBER(6)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
lpad(100000 + mod(lpad(trunc(dbms_random.value(0, substr(899999, 1, 3 - least(3, greatest(''||adj||'' - 0, 0))))), 3 - least(3, greatest(''||adj||'' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 3 + least(3, greatest(''||adj||'' - 0, 0))),0), 3 + least(3, greatest(''||adj||'' - 0, 0)), 0), 899999), 6, 0)
AS NUMBER(6))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "EMPLOYEE_ID" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "EMPLOYEE_ID")) s
where 1=1 and s.orig_val is not null
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_50"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_11_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_12_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 12 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "MGMT_DM_TT_51" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "MGMT_DM_TT_51" PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_12_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_13_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 13 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "COMMISSION_PCT" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 1, 0);
execute immediate ''create table MGMT_DM_TT_51
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(2, 2)) orig_val, CAST(null AS NUMBER(2, 2)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS NUMBER(2, 2))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "COMMISSION_PCT" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "COMMISSION_PCT")) s
,(select rownum rn, new_val from (select distinct "COMMISSION_PCT" new_val from "HR_TEST"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0
where 1=1 and s.orig_val is not null
and s.rn = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_51"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
');
EXECUTE IMMEDIATE 'declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "COMMISSION_PCT" from "HR_TEST"."EMPLOYEES");
num := adj;
adj := greatest(adj - 1, 0);
execute immediate ''create table MGMT_DM_TT_51
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(2, 2)) orig_val, CAST(null AS NUMBER(2, 2)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
a0.new_val
AS NUMBER(2, 2))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, ''||num||'', 0) new_num
from (select "COMMISSION_PCT" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR_TEST"."EMPLOYEES" group by "COMMISSION_PCT")) s
,(select rownum rn, new_val from (select distinct "COMMISSION_PCT" new_val from "HR_TEST"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0
where 1=1 and s.orig_val is not null
and s.rn = a0.rn
'';
DBMS_STATS.GATHER_TABLE_STATS(NULL, ''"MGMT_DM_TT_51"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_13_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_14_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 14 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''DEPARTMENTS'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''DEPARTMENTS'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_14_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_15_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 15 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''DEPARTMENTS'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''DEPARTMENTS'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_15_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_16_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 16 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP INDEX "HR_TEST"."DEPT_ID_PK"');
EXECUTE IMMEDIATE 'DROP INDEX "HR_TEST"."DEPT_ID_PK"';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_16_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_17_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 17 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''EMPLOYEES'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''EMPLOYEES'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_17_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_18_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 18 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''EMPLOYEES'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''EMPLOYEES'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_18_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_19_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 19 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP INDEX "HR_TEST"."EMP_EMAIL_UK"');
EXECUTE IMMEDIATE 'DROP INDEX "HR_TEST"."EMP_EMAIL_UK"';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_19_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_20_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 20 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP INDEX "HR_TEST"."EMP_EMP_ID_PK"');
EXECUTE IMMEDIATE 'DROP INDEX "HR_TEST"."EMP_EMP_ID_PK"';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_20_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_21_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 21 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''JOB_HISTORY'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''JOB_HISTORY'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_21_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_22_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 22 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''JOB_HISTORY'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''JOB_HISTORY'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_22_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_23_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 23 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP INDEX "HR_TEST"."JHIST_EMP_ID_ST_DATE_PK"');
EXECUTE IMMEDIATE 'DROP INDEX "HR_TEST"."JHIST_EMP_ID_ST_DATE_PK"';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1418 THEN
mgmt$mask_sendMsg ( 'Index already dropped, continuing' );
ELSIF SQLCODE = -942 THEN
mgmt$mask_sendMsg ( 'Table or view already dropped, continuing' );
ELSE
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END IF;
END;
END mgmt$step_23_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_24_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 24 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''MANAGERS'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = ''HR_TEST'' and
refd.table_name = ''MANAGERS'' and
refr.constraint_type = ''R'' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || fk.owner || ''"."'' ||
fk.table_name || ''" DROP CONSTRAINT "'' || fk.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_24_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_25_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 25 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''MANAGERS'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;');
EXECUTE IMMEDIATE 'DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = ''HR_TEST'' and
table_name = ''MANAGERS'' and
constraint_type <> ''R'';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE ''ALTER TABLE "'' || c.owner || ''"."'' ||
c.table_name || ''" DROP CONSTRAINT "'' || c.constraint_name || ''"'';
END LOOP;
END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_25_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_26_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 26 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."MANAGERS" RENAME TO "MANAGERS$DMASK"');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."MANAGERS" RENAME TO "MANAGERS$DMASK"';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_26_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_27_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 27 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE TABLE "HR_TEST"."MANAGERS" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c0m50.NEW_VAL "MGR_ID", s."MGR_COST_CENTER", s."APPROVAL_LIMIT" FROM "HR_TEST"."MANAGERS$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."MGR_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ');
EXECUTE IMMEDIATE 'CREATE TABLE "HR_TEST"."MANAGERS" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c0m50.NEW_VAL "MGR_ID", s."MGR_COST_CENTER", s."APPROVAL_LIMIT" FROM "HR_TEST"."MANAGERS$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."MGR_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_27_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_28_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 28 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."MANAGERS" LOGGING NOPARALLEL ');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."MANAGERS" LOGGING NOPARALLEL ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_28_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_29_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 29 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "HR_TEST"."MANAGERS$DMASK" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "HR_TEST"."MANAGERS$DMASK" PURGE';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_29_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_30_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 30 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."MANAGERS"."MGR_ID" IS ''MASK candidate: HR Benefits Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."MANAGERS"."MGR_ID" IS ''MASK candidate: HR Benefits Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_30_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_31_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 31 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"MANAGERS"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;');
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"MANAGERS"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_31_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_32_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 32 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" RENAME TO "JOB_HISTORY$DMASK"');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" RENAME TO "JOB_HISTORY$DMASK"';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_32_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_33_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 33 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE TABLE "HR_TEST"."JOB_HISTORY" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c0m50.NEW_VAL "EMPLOYEE_ID", s."START_DATE", s."END_DATE", s."JOB_ID", s."DEPARTMENT_ID" FROM "HR_TEST"."JOB_HISTORY$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."EMPLOYEE_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ');
EXECUTE IMMEDIATE 'CREATE TABLE "HR_TEST"."JOB_HISTORY" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c0m50.NEW_VAL "EMPLOYEE_ID", s."START_DATE", s."END_DATE", s."JOB_ID", s."DEPARTMENT_ID" FROM "HR_TEST"."JOB_HISTORY$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."EMPLOYEE_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_33_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_34_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 34 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" LOGGING NOPARALLEL ');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" LOGGING NOPARALLEL ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_34_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_35_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 35 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "HR_TEST"."JOB_HISTORY$DMASK" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "HR_TEST"."JOB_HISTORY$DMASK" PURGE';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_35_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_36_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 36 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE UNIQUE INDEX "HR_TEST"."JHIST_EMP_ID_ST_DATE_PK" ON "HR_TEST"."JOB_HISTORY" ("EMPLOYEE_ID", "START_DATE") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
');
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX "HR_TEST"."JHIST_EMP_ID_ST_DATE_PK" ON "HR_TEST"."JOB_HISTORY" ("EMPLOYEE_ID", "START_DATE") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_36_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_37_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 37 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_DATE_INTERVAL" CHECK (end_date > start_date) )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_DATE_INTERVAL" CHECK (end_date > start_date) )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_37_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_38_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 38 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("EMPLOYEE_ID" CONSTRAINT "JHIST_EMPLOYEE_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("EMPLOYEE_ID" CONSTRAINT "JHIST_EMPLOYEE_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_38_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_39_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 39 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_ID_ST_DATE_PK" PRIMARY KEY ("EMPLOYEE_ID", "START_DATE") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_ID_ST_DATE_PK" PRIMARY KEY ("EMPLOYEE_ID", "START_DATE") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_39_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_40_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 40 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("END_DATE" CONSTRAINT "JHIST_END_DATE_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("END_DATE" CONSTRAINT "JHIST_END_DATE_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_40_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_41_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 41 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("JOB_ID" CONSTRAINT "JHIST_JOB_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("JOB_ID" CONSTRAINT "JHIST_JOB_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_41_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_42_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 42 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("START_DATE" CONSTRAINT "JHIST_START_DATE_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" MODIFY ("START_DATE" CONSTRAINT "JHIST_START_DATE_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_42_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_43_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 43 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR_TEST"."JOBS" ("JOB_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR_TEST"."JOBS" ("JOB_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_43_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_44_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 44 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."DEPARTMENT_ID" IS ''Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."DEPARTMENT_ID" IS ''Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_44_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_45_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 45 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."EMPLOYEE_ID" IS ''A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."EMPLOYEE_ID" IS ''A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_45_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_46_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 46 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."END_DATE" IS ''Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."END_DATE" IS ''Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_46_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_47_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 47 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."JOB_ID" IS ''Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."JOB_ID" IS ''Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_47_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_48_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 48 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."START_DATE" IS ''A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."JOB_HISTORY"."START_DATE" IS ''A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_48_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_49_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 49 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON TABLE "HR_TEST"."JOB_HISTORY" IS ''Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.''');
EXECUTE IMMEDIATE 'COMMENT ON TABLE "HR_TEST"."JOB_HISTORY" IS ''Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_49_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_50_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 50 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"JOB_HISTORY"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;');
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"JOB_HISTORY"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_50_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_51_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 51 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" RENAME TO "EMPLOYEES$DMASK"');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" RENAME TO "EMPLOYEES$DMASK"';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_51_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_52_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 52 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE TABLE "HR_TEST"."EMPLOYEES" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c5m50.NEW_VAL "EMPLOYEE_ID", c2m47.NEW_VAL "FIRST_NAME", c4m49.NEW_VAL "LAST_NAME", s."EMAIL", s."PHONE_NUMBER", s."HIRE_DATE", s."JOB_ID", c3m48.NEW_VAL "SALARY", c6m51.NEW_VAL "COMMISSION_PCT", c0m50.NEW_VAL "MANAGER_ID", s."DEPARTMENT_ID", c1m46.NEW_VAL "NATIONAL_ID", s."STREET_ADDRESS", s."POSTAL_CODE", s."CITY", s."STATE_PROVINCE", s."COUNTRY_ID" FROM "HR_TEST"."EMPLOYEES$DMASK" s , MGMT_DM_TT_50 c0m50, MGMT_DM_TT_46 c1m46, MGMT_DM_TT_47 c2m47, MGMT_DM_TT_48 c3m48, MGMT_DM_TT_49 c4m49, MGMT_DM_TT_50 c5m50, MGMT_DM_TT_51 c6m51 WHERE sys_op_map_nonnull(s."MANAGER_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) AND sys_op_map_nonnull(s."NATIONAL_ID") = sys_op_map_nonnull(c1m46.ORIG_VAL) AND sys_op_map_nonnull(s."FIRST_NAME") = sys_op_map_nonnull(c2m47.ORIG_VAL) AND sys_op_map_nonnull(s."SALARY") = sys_op_map_nonnull(c3m48.ORIG_VAL) AND sys_op_map_nonnull(s."LAST_NAME") = sys_op_map_nonnull(c4m49.ORIG_VAL) AND sys_op_map_nonnull(s."EMPLOYEE_ID") = sys_op_map_nonnull(c5m50.ORIG_VAL) AND sys_op_map_nonnull(s."COMMISSION_PCT") = sys_op_map_nonnull(c6m51.ORIG_VAL) ');
EXECUTE IMMEDIATE 'CREATE TABLE "HR_TEST"."EMPLOYEES" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT c5m50.NEW_VAL "EMPLOYEE_ID", c2m47.NEW_VAL "FIRST_NAME", c4m49.NEW_VAL "LAST_NAME", s."EMAIL", s."PHONE_NUMBER", s."HIRE_DATE", s."JOB_ID", c3m48.NEW_VAL "SALARY", c6m51.NEW_VAL "COMMISSION_PCT", c0m50.NEW_VAL "MANAGER_ID", s."DEPARTMENT_ID", c1m46.NEW_VAL "NATIONAL_ID", s."STREET_ADDRESS", s."POSTAL_CODE", s."CITY", s."STATE_PROVINCE", s."COUNTRY_ID" FROM "HR_TEST"."EMPLOYEES$DMASK" s , MGMT_DM_TT_50 c0m50, MGMT_DM_TT_46 c1m46, MGMT_DM_TT_47 c2m47, MGMT_DM_TT_48 c3m48, MGMT_DM_TT_49 c4m49, MGMT_DM_TT_50 c5m50, MGMT_DM_TT_51 c6m51 WHERE sys_op_map_nonnull(s."MANAGER_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) AND sys_op_map_nonnull(s."NATIONAL_ID") = sys_op_map_nonnull(c1m46.ORIG_VAL) AND sys_op_map_nonnull(s."FIRST_NAME") = sys_op_map_nonnull(c2m47.ORIG_VAL) AND sys_op_map_nonnull(s."SALARY") = sys_op_map_nonnull(c3m48.ORIG_VAL) AND sys_op_map_nonnull(s."LAST_NAME") = sys_op_map_nonnull(c4m49.ORIG_VAL) AND sys_op_map_nonnull(s."EMPLOYEE_ID") = sys_op_map_nonnull(c5m50.ORIG_VAL) AND sys_op_map_nonnull(s."COMMISSION_PCT") = sys_op_map_nonnull(c6m51.ORIG_VAL) ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_52_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_53_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 53 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" LOGGING NOPARALLEL ');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" LOGGING NOPARALLEL ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_53_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_54_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 54 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "HR_TEST"."EMPLOYEES$DMASK" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "HR_TEST"."EMPLOYEES$DMASK" PURGE';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_54_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_55_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 55 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE UNIQUE INDEX "HR_TEST"."EMP_EMAIL_UK" ON "HR_TEST"."EMPLOYEES" ("EMAIL") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
');
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX "HR_TEST"."EMP_EMAIL_UK" ON "HR_TEST"."EMPLOYEES" ("EMAIL") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_55_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_56_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 56 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE UNIQUE INDEX "HR_TEST"."EMP_EMP_ID_PK" ON "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
');
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX "HR_TEST"."EMP_EMP_ID_PK" ON "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_56_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_57_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 57 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_57_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_58_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 58 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_58_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_59_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 59 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_59_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_60_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 60 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_60_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_61_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 61 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_61_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_62_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 62 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_62_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_63_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 63 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_63_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_64_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 64 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_64_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_65_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 65 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR_TEST"."JOBS" ("JOB_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR_TEST"."JOBS" ("JOB_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_65_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_66_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 66 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_66_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_67_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 67 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."COMMISSION_PCT" IS ''MASK candidate: HR Benefits Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."COMMISSION_PCT" IS ''MASK candidate: HR Benefits Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_67_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_68_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 68 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."DEPARTMENT_ID" IS ''Department id where employee works; foreign key to department_id
column of the departments table''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."DEPARTMENT_ID" IS ''Department id where employee works; foreign key to department_id
column of the departments table''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_68_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_69_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 69 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."EMAIL" IS ''MASK candidate: HR Privacy Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."EMAIL" IS ''MASK candidate: HR Privacy Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_69_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_70_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 70 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."EMPLOYEE_ID" IS ''MASK candidate: HR Benefits Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."EMPLOYEE_ID" IS ''MASK candidate: HR Benefits Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_70_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_71_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 71 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."FIRST_NAME" IS ''MASK candidate: HR Privacy Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."FIRST_NAME" IS ''MASK candidate: HR Privacy Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_71_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_72_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 72 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."HIRE_DATE" IS ''Date when the employee started on this job. A not null column.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."HIRE_DATE" IS ''Date when the employee started on this job. A not null column.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_72_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_73_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 73 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."JOB_ID" IS ''Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."JOB_ID" IS ''Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_73_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_74_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 74 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."LAST_NAME" IS ''MASK candidate: HR Privacy Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."LAST_NAME" IS ''MASK candidate: HR Privacy Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_74_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_75_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 75 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."MANAGER_ID" IS ''Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."MANAGER_ID" IS ''Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_75_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_76_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 76 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."PHONE_NUMBER" IS ''MASK candidate: HR Privacy Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."PHONE_NUMBER" IS ''MASK candidate: HR Privacy Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_76_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_77_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 77 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."SALARY" IS ''MASK candidate: HR Compensation Policy''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."EMPLOYEES"."SALARY" IS ''MASK candidate: HR Compensation Policy''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_77_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_78_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 78 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON TABLE "HR_TEST"."EMPLOYEES" IS ''employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.''');
EXECUTE IMMEDIATE 'COMMENT ON TABLE "HR_TEST"."EMPLOYEES" IS ''employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_78_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_79_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 79 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"EMPLOYEES"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;');
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"EMPLOYEES"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_79_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_80_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 80 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" RENAME TO "DEPARTMENTS$DMASK"');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" RENAME TO "DEPARTMENTS$DMASK"';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_80_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_81_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 81 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE TABLE "HR_TEST"."DEPARTMENTS" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT s."DEPARTMENT_ID", s."DEPARTMENT_NAME", c0m50.NEW_VAL "MANAGER_ID", s."LOCATION_ID" FROM "HR_TEST"."DEPARTMENTS$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."MANAGER_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ');
EXECUTE IMMEDIATE 'CREATE TABLE "HR_TEST"."DEPARTMENTS" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT s."DEPARTMENT_ID", s."DEPARTMENT_NAME", c0m50.NEW_VAL "MANAGER_ID", s."LOCATION_ID" FROM "HR_TEST"."DEPARTMENTS$DMASK" s , MGMT_DM_TT_50 c0m50 WHERE sys_op_map_nonnull(s."MANAGER_ID") = sys_op_map_nonnull(c0m50.ORIG_VAL) ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_81_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_82_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 82 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" LOGGING NOPARALLEL ');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" LOGGING NOPARALLEL ';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_82_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_83_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 83 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('DROP TABLE "HR_TEST"."DEPARTMENTS$DMASK" PURGE');
EXECUTE IMMEDIATE 'DROP TABLE "HR_TEST"."DEPARTMENTS$DMASK" PURGE';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_83_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_84_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 84 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('CREATE UNIQUE INDEX "HR_TEST"."DEPT_ID_PK" ON "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
');
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX "HR_TEST"."DEPT_ID_PK" ON "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_84_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_85_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 85 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_85_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_86_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 86 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" MODIFY ("DEPARTMENT_NAME" CONSTRAINT "DEPT_NAME_NN" NOT NULL )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" MODIFY ("DEPARTMENT_NAME" CONSTRAINT "DEPT_NAME_NN" NOT NULL )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_86_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_87_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 87 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR_TEST"."EMPLOYEES" ("EMPLOYEE_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_87_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_88_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 88 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "HR_TEST"."LOCATIONS" ("LOCATION_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "HR_TEST"."LOCATIONS" ("LOCATION_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_88_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_89_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 89 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."EMPLOYEES" ADD (CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_89_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_90_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 90 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") )');
EXECUTE IMMEDIATE 'ALTER TABLE "HR_TEST"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR_TEST"."DEPARTMENTS" ("DEPARTMENT_ID") )';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_90_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_91_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 91 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."DEPARTMENT_ID" IS ''Primary key column of departments table.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."DEPARTMENT_ID" IS ''Primary key column of departments table.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_91_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_92_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 92 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."DEPARTMENT_NAME" IS ''A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."DEPARTMENT_NAME" IS ''A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_92_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_93_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 93 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."LOCATION_ID" IS ''Location id where a department is located. Foreign key to location_id column of locations table.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."LOCATION_ID" IS ''Location id where a department is located. Foreign key to location_id column of locations table.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_93_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_94_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 94 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."MANAGER_ID" IS ''Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.''');
EXECUTE IMMEDIATE 'COMMENT ON COLUMN "HR_TEST"."DEPARTMENTS"."MANAGER_ID" IS ''Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_94_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_95_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 95 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('COMMENT ON TABLE "HR_TEST"."DEPARTMENTS" IS ''Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.''');
EXECUTE IMMEDIATE 'COMMENT ON TABLE "HR_TEST"."DEPARTMENTS" IS ''Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.''';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_95_41;
/
CREATE OR REPLACE PROCEDURE mgmt$step_96_41(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER)
AUTHID CURRENT_USER IS
sqlerr_msg VARCHAR2(100);
BEGIN
IF step_num <> 96 THEN
return;
END IF;
mgmt$mask_setStep (41, 'MGMT$MASK_CHECKPOINT', step_num);
step_num := step_num + 1;
BEGIN
mgmt$mask_sendMsg ('BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"DEPARTMENTS"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;');
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''"HR_TEST"'', ''"DEPARTMENTS"'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;';
EXCEPTION
WHEN OTHERS THEN
sqlerr_msg := SUBSTR(SQLERRM, 1, 100);
mgmt$mask_errorExitOraError('ERROR executing steps ', sqlerr_msg);
step_num := -1;
return;
END;
END mgmt$step_96_41;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_cleanup_41 (script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER, highest_step IN INTEGER)
AUTHID CURRENT_USER IS
BEGIN
IF step_num <= highest_step THEN
return;
END IF;
mgmt$mask_sendMsg ('Starting cleanup of recovery tables');
mgmt$mask_deleteJobTableEntry(script_id, job_table, step_num, highest_step);
mgmt$mask_sendMsg ('Completed cleanup of recovery tables');
END mgmt$mask_cleanup_41;
/
CREATE OR REPLACE PROCEDURE mgmt$mask_commentheader_41 IS
BEGIN
mgmt$mask_sendMsg ('-- Target database: orcl');
mgmt$mask_sendMsg ('-- Script generated at: 14-SEP-2014 17:42');
END mgmt$mask_commentheader_41;
/
-- Script Execution Controller
-- ==============================================
variable step_num number;
exec mgmt$mask_commentheader_41;
exec mgmt$mask_sendMsg ('Starting Data Masking');
show user;
exec mgmt$mask_checkDBAPrivs;
exec mgmt$mask_setupJobTable (41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_1_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_2_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_3_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_4_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_5_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_6_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_7_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_8_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_9_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_10_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_11_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_12_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_13_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_14_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_15_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_16_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_17_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_18_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_19_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_20_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_21_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_22_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_23_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_24_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_25_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_26_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_27_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_28_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_29_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_30_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_31_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_32_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_33_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_34_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_35_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_36_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_37_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_38_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_39_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_40_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_41_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_42_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_43_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_44_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_45_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_46_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_47_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_48_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_49_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_50_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_51_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_52_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_53_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_54_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_55_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_56_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_57_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_58_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_59_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_60_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_61_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_62_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_63_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_64_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_65_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_66_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_67_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_68_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_69_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_70_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_71_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_72_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_73_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_74_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_75_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_76_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_77_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_78_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_79_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_80_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_81_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_82_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_83_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_84_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_85_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_86_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_87_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_88_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_89_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_90_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_91_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_92_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_93_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_94_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_95_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$step_96_41(41, 'MGMT$MASK_CHECKPOINT', :step_num);
exec mgmt$mask_sendMsg ('Completed Data Masking. Starting cleanup phase.');
exec mgmt$mask_cleanup_41 (41, 'MGMT$MASK_CHECKPOINT', :step_num, 96);
exec mgmt$mask_sendMsg ('Starting cleanup of generated procedures');
DROP PROCEDURE mgmt$step_1_41;
DROP PROCEDURE mgmt$step_2_41;
DROP PROCEDURE mgmt$step_3_41;
DROP PROCEDURE mgmt$step_4_41;
DROP PROCEDURE mgmt$step_5_41;
DROP PROCEDURE mgmt$step_6_41;
DROP PROCEDURE mgmt$step_7_41;
DROP PROCEDURE mgmt$step_8_41;
DROP PROCEDURE mgmt$step_9_41;
DROP PROCEDURE mgmt$step_10_41;
DROP PROCEDURE mgmt$step_11_41;
DROP PROCEDURE mgmt$step_12_41;
DROP PROCEDURE mgmt$step_13_41;
DROP PROCEDURE mgmt$step_14_41;
DROP PROCEDURE mgmt$step_15_41;
DROP PROCEDURE mgmt$step_16_41;
DROP PROCEDURE mgmt$step_17_41;
DROP PROCEDURE mgmt$step_18_41;
DROP PROCEDURE mgmt$step_19_41;
DROP PROCEDURE mgmt$step_20_41;
DROP PROCEDURE mgmt$step_21_41;
DROP PROCEDURE mgmt$step_22_41;
DROP PROCEDURE mgmt$step_23_41;
DROP PROCEDURE mgmt$step_24_41;
DROP PROCEDURE mgmt$step_25_41;
DROP PROCEDURE mgmt$step_26_41;
DROP PROCEDURE mgmt$step_27_41;
DROP PROCEDURE mgmt$step_28_41;
DROP PROCEDURE mgmt$step_29_41;
DROP PROCEDURE mgmt$step_30_41;
DROP PROCEDURE mgmt$step_31_41;
DROP PROCEDURE mgmt$step_32_41;
DROP PROCEDURE mgmt$step_33_41;
DROP PROCEDURE mgmt$step_34_41;
DROP PROCEDURE mgmt$step_35_41;
DROP PROCEDURE mgmt$step_36_41;
DROP PROCEDURE mgmt$step_37_41;
DROP PROCEDURE mgmt$step_38_41;
DROP PROCEDURE mgmt$step_39_41;
DROP PROCEDURE mgmt$step_40_41;
DROP PROCEDURE mgmt$step_41_41;
DROP PROCEDURE mgmt$step_42_41;
DROP PROCEDURE mgmt$step_43_41;
DROP PROCEDURE mgmt$step_44_41;
DROP PROCEDURE mgmt$step_45_41;
DROP PROCEDURE mgmt$step_46_41;
DROP PROCEDURE mgmt$step_47_41;
DROP PROCEDURE mgmt$step_48_41;
DROP PROCEDURE mgmt$step_49_41;
DROP PROCEDURE mgmt$step_50_41;
DROP PROCEDURE mgmt$step_51_41;
DROP PROCEDURE mgmt$step_52_41;
DROP PROCEDURE mgmt$step_53_41;
DROP PROCEDURE mgmt$step_54_41;
DROP PROCEDURE mgmt$step_55_41;
DROP PROCEDURE mgmt$step_56_41;
DROP PROCEDURE mgmt$step_57_41;
DROP PROCEDURE mgmt$step_58_41;
DROP PROCEDURE mgmt$step_59_41;
DROP PROCEDURE mgmt$step_60_41;
DROP PROCEDURE mgmt$step_61_41;
DROP PROCEDURE mgmt$step_62_41;
DROP PROCEDURE mgmt$step_63_41;
DROP PROCEDURE mgmt$step_64_41;
DROP PROCEDURE mgmt$step_65_41;
DROP PROCEDURE mgmt$step_66_41;
DROP PROCEDURE mgmt$step_67_41;
DROP PROCEDURE mgmt$step_68_41;
DROP PROCEDURE mgmt$step_69_41;
DROP PROCEDURE mgmt$step_70_41;
DROP PROCEDURE mgmt$step_71_41;
DROP PROCEDURE mgmt$step_72_41;
DROP PROCEDURE mgmt$step_73_41;
DROP PROCEDURE mgmt$step_74_41;
DROP PROCEDURE mgmt$step_75_41;
DROP PROCEDURE mgmt$step_76_41;
DROP PROCEDURE mgmt$step_77_41;
DROP PROCEDURE mgmt$step_78_41;
DROP PROCEDURE mgmt$step_79_41;
DROP PROCEDURE mgmt$step_80_41;
DROP PROCEDURE mgmt$step_81_41;
DROP PROCEDURE mgmt$step_82_41;
DROP PROCEDURE mgmt$step_83_41;
DROP PROCEDURE mgmt$step_84_41;
DROP PROCEDURE mgmt$step_85_41;
DROP PROCEDURE mgmt$step_86_41;
DROP PROCEDURE mgmt$step_87_41;
DROP PROCEDURE mgmt$step_88_41;
DROP PROCEDURE mgmt$step_89_41;
DROP PROCEDURE mgmt$step_90_41;
DROP PROCEDURE mgmt$step_91_41;
DROP PROCEDURE mgmt$step_92_41;
DROP PROCEDURE mgmt$step_93_41;
DROP PROCEDURE mgmt$step_94_41;
DROP PROCEDURE mgmt$step_95_41;
DROP PROCEDURE mgmt$step_96_41;
DROP PROCEDURE mgmt$mask_cleanup_41;
DROP PROCEDURE mgmt$mask_commentheader_41;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_48 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_46 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_50 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_47 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_49 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_DM_TT_51 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
NULL;
END IF;
END;
/
exec mgmt$mask_sendMsg ('Completed cleanup of generated procedures');
exec mgmt$mask_sendMsg ('Script execution complete');
spool off
set pagesize 24
set serveroutput off
set feedback on
set echo on
set ver on
三、使用Data Masking 后的效果
3.1原始数据：
详情点击：displayContents bafort.html
3.2 Data Masking处理后数据：
详情点击：displayContents.html
四、Data Masking创建的详细实验步骤：
详情点击：
http://www.oracle.com/webfolder/technetwork/tutorials/obe/em/emgc10205/data_masking/datamask.htm
Creating Tables for the Tutorial
To create tables to use during this data masking tutorial, perform the following steps:
- Log in to SQL*Plus as the SYSTEM user.
2 . Execute the OBE_DM_setup01.sql script to prepare for this tutorial. This script deletes objects that may have been created during a previous execution of the tutorial.
3 . Execute the OBE_DM_setup02.sql script to create the HR_TEST and OE_TEST schemas. This step simulates cloning the production database to an instance that is used for masking, but much smaller in scope. Enter a password for the HR_TEST and OE_TEST users when prompted.
4 . The HR_TEST and OE_TEST users are created and objects are created in their schemas.
- Execute the OBE_DM_setup03.sql script to add columns and data to the HR_TEST.EMPLOYEES table.
6 . The script also creates a table named HR_TEST.MASK_DATA which is used to illustrate how you use a data table from a commercial provider.
Identifying Sensitive Data
In this section you will view data in the HR_TEST.EMPLOYEES table to determine which columns should be masked.
-
Launch Enterprise Manager Database Control by entering the following URL: https://
:1158/em
Note: If you are using Enterprise Manager Grid Control 10.2.0.5, specify the appropriate URL for your environment. -
Enter the following information to log in to Enterprise Manager Database Control:
Username: SYS
Password: ********
Connect As: SYSDBA
Note: If you are using Enterprise Manager Grid Control, login using the SYSMAN database account.
3 . Click the Schema tab on the Database Instance Home page.
Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link. You may be asked to set the preferred database credential. If so, use the SYSTEM database account.
4 . Click Tables in the Database Objects region.
5 . Enter the following information:
Schema: hr_test
Object Name: employees
Click Go.
6 . Select View Data in the Actions list. Click Go.
7 . View the data in the HR_TEST.EMPLOYEES table to determine likely candidates for data masking. For ease of comparison after perform the data masking operation, click EMAIL to sort the rows on the EMAIL column.
8 . The rows are now sorted based on the EMAIL column. Keep this browser window open for comparison with the data after it has been masked. In the next section, you will begin masking the columns that you have identified as containing sensitive data.
Creating Masking Definitions: EMPLOYEE_ID Column
You have been informed that the EMPLOYEES.EMPLOYEE_ID column contains sensitive data. In this section you create a masking definition for the EMPLOYEE_ID column of the HR_TEST.EMPLOYEES table. You must also add a dependent column to the masking definition. The MANAGERS.MGR_ID column is not declared as a foreign key, but is dependent on EMPLOYEES.EMPLOYEE_ID at the application level.
-
Open a new browser window or tab, and launch Enterprise Manager Database Control by entering the following URL: https://
:1158/em
Note: If you are using Enterprise Manager Grid Control 10.2.0.5, specify the appropriate URL for your environment. -
Enter the following information to log in to Enterprise Manager Database Control:
Username: SYS
Password: *********
Connect As: SYSDBA
Click Login.
Note: If you are using Enterprise Manager Grid Control, login using the SYSMAN database account.
3 . On the Database Instance Home page, click the Schema tab.
Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link.
4 . Click Definitions in the Data Masking section.
5 . On the Data Masking Definitions page, Click Create.
6 . On the Create Masking Definition page, enter the following:
Name: HR Employee Mask
Description: HR Employee Masking Policy
In the Columns section, click Add.
7 . On the Add Columns page, enter the following:
Schema: hr_test
Table Name: employees
Click Search.
8 . Notice that the Comment column contains information supplied by the application DBA indicating which columns are mask candidates. Select the EMPLOYEE_ID column. Click Add.
9 . Note that the foreign key columns were automatically added to the masking definition. On the Create Masking Definition page, click the + icon under the Dependent Columns heading.
10 . On the Add Dependent Columns page, enter the following:
Schema: hr_test
Table Name: managers
Click Search.
11 . Select the MGR_ID column. Click Add.
12 . On the Create Masking Definition page, click the icon under the Format heading.
13 . On the Define Column Mask page, select Random Numbers from the Format Entry list. Click Add.
14 . Enter the following information:
Start value: 100000
End value: 999999
Click the icon in the Sample column to view sample data.
15 . Sample data is displayed. You can click the icon again to view additional sample values. Click OK when you have finished viewing sample values.
16 . On the Create Masking Definition page, click OK.
17 . The Data Masking Definitions page is displayed showing the HR Employee Mask masking definition.
Creating Masking Definitions: FIRST_NAME and LAST_NAME Columns
Creating Masking Formats for FIRST_NAME and LAST_NAME
Create the masking formats for the EMPLOYEES.FIRST_NAME and EMPLOYEES.LAST_NAME columns by using the HR_TEST.MASK_DATA table as the source of masking data. These steps illustrate how you would use a data table from a commercial data provider to mask confidential data such as names.
- On the Data Masking Definitions page, click the Format Library link.
- On the Format Library page, click Create.
3 . On the Create Format page, enter the following information:
Name: Anglo-American First Name
Description: Masking format for first name
Select Table Column in the list and click Go.
4 . On the Create Format page, enter the following information:
Table Name: hr_test.mask_data
Column Name: first_name
Click OK.
5 . On the Create Format page, click OK.
6 . A confirmation message is displayed on the Format Library page. Click Create.
7 . On the Create Format page, enter the following information:
Name: Anglo-American Last Name
Description: Masking format for last name
Select Table Column in the list and click Go.
8 . On the Create Format page, enter the following information:
Table Name: hr_test.mask_data
Column Name: last_name
Click OK.
9 . On the Create Format page, click OK.
10 . A confirmation message is displayed on the Format Library page.
Creating Masking Definitions for FIRST_NAME and LAST_NAME
Create the masking definitions for the EMPLOYEES.FIRST_NAME and EMPLOYEES.LAST_NAME columns. Use the mask formats that you defined in the previous step.
- On the Format Library page, click the Data Masking Definitions link.
- On the Data Masking Definitions page, select HR Employee Mask. Click Edit.
3 . On the Edit Masking Definition: HR Employee Mask page, click Add.
4 . On the Add Columns page, enter the following information:
Schema: hr_test
Table Name: employees
Click Search.
5 . On the Add Columns page, select the FIRST_NAME and LAST_NAME columns. Click Add.
6 . On the Edit Masking Definition: HR Employee Mask page, click the Format icon in the FIRST_NAME row.
7 . On the Define Column Mask page, click Import Format.
8 . On the Import Format page, select Anglo-American First Name. Click Import.
9 . Click the icon in the Sample column to view sample data.
10 . On the Define Column Mask page, click OK.
11 . On the Edit Masking Definition: HR Employee Mask page, click the Format icon in the LAST_NAME row.
12 . On the Define Column Mask page, click Import Format.
13 . On the Import Format page, select Anglo-American Last Name. Click Import.
14 . On the Define Column Mask page, click the icon in the Sample column to view sample data.
15 . On the Define Column Mask page, click OK.
16 . On the Edit Masking Definition: HR Employee Mask page, click OK.
17 . On the Data Masking Definitions page, observe that four columns are masked.
Creating Masking Definitions: SALARY Column
Add the EMPLOYEES.SALARY column to the HR Employee Mask masking definition and specify the Shuffle mask format. The Shuffle format is used to shuffle the values in the column amongst the rows.
- On the Data Masking Definitions page, select HR Employee Mask. Click Edit.
- On the Edit Masking Definition: HR Employee Mask page, click Add.
3 . On the Add Columns page, enter the following information:
Schema: hr_test
Table Name: employees
Click Search.
4 . On the Add Columns page, select the SALARY column. Click Define Format and Add.
5 . On the Define Column Mask page, select Shuffle from the Format Entry list. Click Add.
6 . On the Define Column Mask page, click the icon in the Sample column to view sample data.
7 . On the Define Column Mask page, click OK.
8 . On the Edit Masking Definition: HR Employee Mask page, click OK.
**Creating Masking Definitions: COMMISSION_PCT Column **
Add the EMPLOYEES.COMMISSION_PCT column to the HR Employee Mask masking definition and specify the Shuffle mask format.
- On the Data Masking Definitions page, select HR Employee Mask. Click Edit.
- On the Edit Masking Definition: HR Employee Mask page, click Add.
3 . On the Add Columns page, enter the following information:
Schema: hr_test
Table Name: employees
Click Search.
4 . On the Add Columns page, select the COMMISSION_PCT column. Click Define Format and Add.
5 . On the Define Column Mask page, select Shuffle from the Format Entry list. Click Add.
6 . On the Define Column Mask page, click the icon in the Sample column to view sample data.
7 . On the Define Column Mask page, click OK.
8 . On the Edit Masking Definition: HR Employee Mask page, click OK.
Creating Masking Definitions: NATIONAL_ID Column
Implement condition-based masking for the NATIONAL_ID column. Configure the masking so that the NATIONAL_ID column is masked with the National Insurance Number Formatted format for UK employees and Social Security Number Formatted for US employees. The NATIONAL_ID column for employees from other countries does not need to be masked.
1 . On the Data Masking Definitions page, select HR Employee Mask. Click Edit.
2 . On the Edit Masking Definition: HR Employee Mask page, click Add in the Columns section.
3 . On the Add Columns page, enter the following information:
Schema: hr_test
Table Name: employees
Click Search.
4 . Select the NATIONAL_ID column. Click Define Format and Add.
5 . On the Define Column Mask page, click Add Condition.
6 . On the Define Column Mask page, enter the following SQL query in the Condition field:
national_id in
(select national_id from hr_test.employees where country_id = ‘UK’)
Click Import Format.
7 . On the Import Format page, select National Insurance Number Formatted. Click Import.
8 . On the Define Column Mask page, click the icon in the Sample column to view sample data.
9 . On the Define Column Mask page, click Add Condition.
10 . On the Define Column Mask page, enter the following SQL query in the Condition field:
national_id in
(select national_id from hr_test.employees where country_id = ‘US’)
Click Import Format.
11 . On the Import Format page, select Social Security Number Formatted. Click Import.
12 . On the Define Column Mask page, click the icon in the Sample column to view sample data.
13 . On the Define Column Mask page, select Default Condition. Select Preserve Original Data in the Format Entry list. Click Add.
14 . On the Define Column Mask page, click OK.
15 . On the Edit Masking Definition: HR Employee Mask page, click OK.
16 . On the Data Masking Definitions page, observe that seven columns are defined for masking.
Performing the Data Masking Operation
Use Enterprise Manager Database Control to generate the data-masking script and schedule the data masking job.
1 . On the Data Masking Definitions page, select HR Employee Mask. Click Generate Script.
2 . The Processing: Generating Data Masking Script page is displayed.
3 . A message is displayed indicating that the script has been generated.
4 . Scroll down the Script Generation Results: HR Employee Mask page. Expand Impact Report.
5 . View the Impact Report and verify that there are no errors. Click Schedule Job.
6 . On the Schedule Data Masking Job: HR Employee Mask page, enter the host credentials. Select Immediately in the Start section. Click Submit.
7 . On the Data Masking Definitions page, a message is displayed indicating that the job has been submitted. Click View Job Details.
8 . On the Job Run: MASKING_JOB_NNN page, verify that the Status is Succeeded. Click Database to return to the Database Home page.
Querying Masked Data
Now view the data that was masked and compare the results with the unmasked data.
1 . Click the Schema tab on the Database Instance Home page.
Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link.
2 . Click Tables in the Database Objects region.
3 . Enter the following information:
Schema: hr_test
Object Name: employees
Click Go.
4 . Select View Data in the Actions list. Click Go.
5 . View the masked data in the HR_TEST.EMPLOYEES table. ClickEMAIL to sort the rows on the EMAIL column.
6 . Compare the values in the columns with the values you viewed prior to masking. Refer to your other Enterprise Manager Database Control window for the comparison.
7 . Click OK to return to the Tables page.
8 . Click the Database tab to return to the Database Instance Home page.
Removing Objects Created During this Tutorial
To clean up following this tutorial, perform the following steps.
1 . Return to your SQLPlus window. Logged in to SQLPlus as the SYSTEM user, execute the OBE_DM_cleanup.sql script.
2 . Return to Enterprise Manager Database Control. Click the Schema tab. Click Definitions in the Data Masking section. On the Data Masking Definitions page, select the HR Employee Mask and click Delete.
3 . The HR Employee Mask masking definition is deleted from the Enterprise Manager Database Control repository.
Summary
In this tutorial, you have learned how to:
• Use Oracle-supplied masking formats from the Format Library
• Create masking formats
• Create masking definitions
• Generate the masking script
• Schedule the data masking job
五、总结
使用Data Masking可以直接替换生产库数据；可以在EM里面用自己定义的Data Masking克隆一个数据库（用作测试）；也可以创建一个新的数据库，先在生产库上定义Data Masking规则，把生产数据导入测试库，然后再用Data Masking生成的脚本在测试库上面跑(如果测试库也是用EM 管理的话可以在EM里面直接调用Data Masking转换隐私数据)。Data Masking也可以从一个EM里面导出来，导入到另外一个EM，这样在另一个EM里面我们就可以直接用了。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
