In order to use this script you need to have created a database link in your test environment called NOETIX_TEST that points to your production environment.
The SQL is;
declare
v_Version varchar2(10) := '0.9.5';
-- 0.9.2 Added last updated by, last updated date columns to the column source comparrison
-- 0.9.3 Removed A$ column from the REMOVED/ADDED query, "Query position" no longer a separate line in report,
-- additional highlighting of viewname added, Local/Remote DB name now used rather than PROD and TEST
-- 0.9.4 Adding in a check for WHERE clause changes
-- 0.9.5 For some reason when a column changed type (i.e. COL to EXPR) I'd decided not to report it. Fixed.
v_ShowDetails varchar2(1) := 'T';
v_ShowDangerItemsOnly varchar2(1) := 'F';
v_LocalDB V$DATABASE.NAME%TYPE;
v_RemoteDB V$DATABASE.NAME%TYPE;
v_ViewCount integer;
v_ChangeCount integer;
v_OldViewName n_views.view_name%TYPE;
begin
dbms_output.put_line('Noetix Schema Comparison ' ||
TO_CHAR(SYSDATE, 'DD-MON-YYYY') || ' (V' ||
v_Version || ')');
dbms_output.put_line('---------------------------------------------');
for v_Database in (SELECT NAME FROM V$DATABASE@NOETIX_TEST) loop
v_RemoteDB := v_Database.name;
dbms_output.put_line('(Remote) Database = ' || v_Database.name);
end loop;
for v_Database in (SELECT NAME FROM V$DATABASE) loop
v_LocalDB := v_Database.name;
dbms_output.put_line('(Local) Database = ' || v_Database.name);
end loop;
dbms_output.put_line(' ');
dbms_output.put_line('SECTION 001- CHECKING VIEWS');
dbms_output.put_line('---------------------------');
if v_ShowDangerItemsOnly = 'F' then
dbms_output.put_line('New Views');
dbms_output.put_line('---------');
v_ChangeCount := 0;
for v_LocalView in (SELECT DISTINCT nv.View_Name
FROM N_Views NV
WHERE EXISTS
(SELECT 'X'
FROM All_Views AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
ORDER BY nv.View_Name) loop
SELECT COUNT(*)
INTO v_ViewCount
FROM N_Views@NOETIX_TEST NV
WHERE EXISTS (SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
AND nv.view_name = v_LocalView.view_name;
if v_ViewCount = 0 then
if v_ShowDetails = 'T' then
dbms_output.put_line(v_LocalView.view_name);
end if;
v_ChangeCount := v_ChangeCount + 1;
end if;
end loop;
dbms_output.put_line('-- Total New View Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
end if;
dbms_output.put_line('Removed Views');
dbms_output.put_line('-------------');
v_ChangeCount := 0;
for v_RemoteView in (SELECT DISTINCT nv.View_Name
FROM N_Views@NOETIX_TEST NV
WHERE EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
ORDER BY nv.View_Name) loop
SELECT COUNT(*)
INTO v_ViewCount
FROM N_Views NV
WHERE EXISTS (SELECT 'X'
FROM All_Views AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
AND nv.view_name = v_RemoteView.view_name;
if v_ViewCount = 0 then
if v_ShowDetails = 'T' then
dbms_output.put_line(v_RemoteView.view_name);
end if;
v_ChangeCount := v_ChangeCount + 1;
end if;
end loop;
dbms_output.put_line('-- Total Removed View Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
dbms_output.put_line('SECTION 002- CHECKING COLUMNS');
dbms_output.put_line('-----------------------------');
dbms_output.put_line('Views With Column Changes');
dbms_output.put_line('-------------------------');
v_ChangeCount := 0;
v_OldViewname := '@';
for v_Column in (select atc.table_name view_name,
'ADDED' change_type,
atc.column_name
from all_tab_columns ATC
where atc.owner = user
and atc.COLUMN_NAME NOT LIKE 'A$%'
and atc.COLUMN_NAME <> 'Z$$_________________________'
and atc.TABLE_NAME IN
(SELECT UPPER(nv.view_name)
FROM N_Views@NOETIX_TEST nv
WHERE NVL(nv.omit_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 'X'
FROM all_tab_columns@NOETIX_TEST ATC1
WHERE ATC.OWNER = ATC1.OWNER
AND ATC.TABLE_NAME = ATC1.TABLE_Name
AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
AND v_ShowDangerItemsOnly = 'F'
UNION
select atc.table_name view_name,
'REMOVED',
atc.column_name
from all_tab_columns@NOETIX_TEST ATC
where atc.owner = user
and atc.COLUMN_NAME NOT LIKE 'A$%'
and atc.COLUMN_NAME <> 'Z$$_________________________'
and atc.TABLE_NAME IN
(SELECT UPPER(nv.view_name)
FROM N_Views nv
WHERE NVL(nv.omit_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 'X'
FROM all_tab_columns ATC1
WHERE ATC.OWNER = ATC1.OWNER
AND ATC.TABLE_NAME = ATC1.TABLE_Name
AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
ORDER BY 1, 2, 3) loop
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_Column.view_name then
v_OldViewname := v_Column.view_name;
dbms_output.put_line(v_Column.view_name);
end if;
if v_ShowDetails = 'T' then
dbms_output.put_line(' ' || INITCAP(v_Column.Change_Type) || ' ' ||
v_Column.Column_Name);
end if;
end loop;
dbms_output.put_line('-- Total Column Changes Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
dbms_output.put_line('Columns With Modified Source');
dbms_output.put_line('----------------------------');
dbms_output.put_line('NOTE: This is a comparison of the Noetix tables, not the view source themselves');
v_ChangeCount := 0;
v_OldViewname := '@';
for v_Column in (SELECT nv.View_Name Test_View_Name,
nv.query_position Test_Query_Position,
nv.column_name Test_column_name,
nv.column_type Test_Column_Type,
NVCT.last_updated_by Test_Last_Updated_By,
NVCT.Last_update_Date Test_Last_Udpated_Date,
NVL(nv.table_alias, '') || '.' ||
Replace(Replace(Replace(nv.column_expression,
' ',
' '),
chr(13),
''),
chr(10),
'') Test_Column_Detail,
nv1.View_Name Prod_View_Name,
nv1.query_position Prod_Query_Position,
nv1.column_name Prod_column_name,
nv1.column_type Prod_Column_Type,
NVCT1.last_updated_by Prod_Last_Updated_By,
NVCT1.Last_update_Date Prod_Last_Udpated_Date,
NVL(nv1.table_alias, '') || '.' ||
Replace(Replace(Replace(nv1.column_expression,
' ',
' '),
chr(13),
''),
chr(10),
'') Prod_Column_Detail,
Replace(Replace(Replace(nv1.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'') || ' > ' ||
Replace(Replace(Replace(nv.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'') Change_Pattern
FROM N_View_Columns NV
JOIN N_View_Column_Templates NVCT
ON NV.T_COLUMN_ID = NVCT.t_Column_Id
JOIN n_view_columns@NOETIX_TEST NV1
ON nv1.view_name = nv.view_name
AND nv1.column_name = nv.column_name
AND nv1.query_position = nv.query_position
AND UPPER(nv1.View_name) IN
(SELECT AV.VIEW_NAME
FROM All_Views AV
WHERE AV.Owner = USER)
AND NVL(NV1.OMIT_FLAG, 'N') = 'N'
AND NVL(nv1.column_expression, 'NULL') NOT LIKE
'%Copyright Noetix Corporation%'
JOIN N_View_Column_Templates@NOETIX_TEST NVCT1
ON NV1.column_label = nvct1.column_label
AND nv1.view_label = nvct1.view_label
AND nv1.query_position = nvct1.query_position
WHERE 1 = 1
AND UPPER(NV.View_Name) NOT LIKE '%_BASE'
AND nv.column_type NOT IN ('GENEXPR')
AND NVL(NV.OMIT_FLAG, 'N') = 'N'
AND NVL(nv.column_expression, 'NULL') NOT LIKE
'%Copyright Noetix Corporation%'
AND UPPER(nv.View_name) IN
(SELECT AV.VIEW_NAME
FROM All_Views@NOETIX_TEST AV
WHERE AV.Owner = USER)
AND NVL(lower(CASE
WHEN nv1.COLUMN_TYPE = 'EXPR' THEN
''
ELSE
NVL(nv1.table_alias, '') || '.'
END || Replace(Replace(Replace(nv1.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'')),
'NULL') <> NVL(lower(CASE
WHEN nv.COLUMN_TYPE = 'EXPR' THEN
''
ELSE
NVL(nv.table_alias, '') || '.'
END || Replace(Replace(Replace(nv.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'')),
'NULL')
ORDER BY nv.View_Name, nv.query_position, nv.column_name) loop
--if v_Column.Test_Column_Type = v_Column.Prod_Column_Type then
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_Column.Test_View_Name then
v_OldViewname := v_Column.Test_View_Name;
dbms_output.put_line('===== ' || UPPER(v_Column.Test_View_Name) ||
' =====');
end if;
dbms_output.put_line(' ' || v_Column.Test_Column_Name || ' (' ||
v_Column.Test_Column_Type || ' Query Position ' ||
v_Column.Test_Query_Position || ')');
if v_ShowDetails = 'T' then
dbms_output.put_line(' ' || v_LocalDB || '=' ||
v_Column.Test_Column_Detail || ' (' ||
v_Column.Test_Last_updated_By || ', ' ||
TO_CHAR(v_Column.Test_Last_Udpated_Date,
'DD-MON-YYYY') || ')');
dbms_output.put_line(' ' || v_RemoteDB || '=' ||
v_Column.Prod_Column_Detail || ' (' ||
v_Column.Prod_Last_updated_By || ', ' ||
TO_CHAR(v_Column.Prod_Last_Udpated_Date,
'DD-MON-YYYY') || ')');
end if;
--end if;
end loop;
dbms_output.put_line('-- Total Modified Column Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line('');
dbms_output.put_line('SECTION 003- CHECKING VIEW COMPOSITION');
dbms_output.put_line('--------------------------------------');
dbms_output.put_line('Changed Where Clause Conditions');
dbms_output.put_line('------------------------------');
v_ChangeCount := 0;
for v_WhereChange in (select 'Added' change_direction,
NVW.view_name,
nvw.query_position,
nvw.where_clause_position,
nvw.where_clause,
nvwt.last_updated_by,
nvwt.last_update_date
FROM N_View_Wheres NVW
JOIN n_View_Where_Templates NVWT
ON NVW.View_Label = NVWT.View_Label
AND nvw.query_position = nvwt.query_position
AND nvw.where_clause_position =
nvwt.where_clause_position
JOIN All_Views AV
ON AV.owner = USER
AND AV.VIEW_NAME = UPPER(NVW.view_name)
WHERE 1 = 1
AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
AND NVL(NVW.Omit_Flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'X'
FROM N_View_Wheres@NOETIX_TEST NVW_R
WHERE NVW.VIEW_NAME = NVW_R.view_name
AND NVW.query_position =
NVW_R.query_position
AND NVW.where_clause = NVW_R.where_clause)
AND EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV1
WHERE AV1.owner = USER
AND AV1.VIEW_NAME = UPPER(NVW.view_name))
UNION
SELECT 'Removed',
NVW.view_name,
nvw.query_position,
nvw.where_clause_position,
nvw.where_clause,
nvwt.last_updated_by,
nvwt.last_update_date
FROM N_View_Wheres@NOETIX_TEST NVW
JOIN n_View_Where_Templates@NOETIX_TEST NVWT
ON NVW.View_Label = NVWT.View_Label
AND nvw.query_position = nvwt.query_position
AND nvw.where_clause_position =
nvwt.where_clause_position
WHERE 1 = 1
AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
AND NVL(NVW.Omit_Flag, 'N') = 'N'
AND EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE AV.owner = USER
AND AV.VIEW_NAME = UPPER(NVW.view_name))
AND NOT EXISTS
(SELECT 'X'
FROM N_View_Wheres NVW_R
WHERE NVW.VIEW_NAME = NVW_R.view_name
AND NVW.query_position =
NVW_R.query_position
AND NVW.where_clause = NVW_R.where_clause)
AND EXISTS
(SELECT 'X'
FROM All_Views AV1
WHERE AV1.owner = USER
AND AV1.VIEW_NAME = UPPER(NVW.view_name))
ORDER BY 2, 3, 4, 5) loop
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_WhereChange.View_Name then
v_OldViewname := v_WhereChange.View_Name;
dbms_output.put_line('===== ' || UPPER(v_WhereChange.View_Name) ||
' =====');
end if;
if v_ShowDetails = 'T' then
dbms_output.put_line(' Query Position ' ||
v_WhereChange.Query_Position || ' ' ||
InitCap(v_WhereChange.Change_Direction) || ': ' ||
v_WhereChange.Where_Clause || ' (Position = ' ||
TO_CHAR(v_WhereChange.Where_Clause_Position) || ', ' ||
v_WhereChange.Last_updated_By || ', ' ||
TO_CHAR(v_WhereChange.Last_Update_Date,
'DD-MON-YYYY') || ')');
end if;
end loop;
dbms_output.put_line('-- Total Where Clause Changes = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
rollback;
end;
v_Version varchar2(10) := '0.9.5';
-- 0.9.2 Added last updated by, last updated date columns to the column source comparrison
-- 0.9.3 Removed A$ column from the REMOVED/ADDED query, "Query position" no longer a separate line in report,
-- additional highlighting of viewname added, Local/Remote DB name now used rather than PROD and TEST
-- 0.9.4 Adding in a check for WHERE clause changes
-- 0.9.5 For some reason when a column changed type (i.e. COL to EXPR) I'd decided not to report it. Fixed.
v_ShowDetails varchar2(1) := 'T';
v_ShowDangerItemsOnly varchar2(1) := 'F';
v_LocalDB V$DATABASE.NAME%TYPE;
v_RemoteDB V$DATABASE.NAME%TYPE;
v_ViewCount integer;
v_ChangeCount integer;
v_OldViewName n_views.view_name%TYPE;
begin
dbms_output.put_line('Noetix Schema Comparison ' ||
TO_CHAR(SYSDATE, 'DD-MON-YYYY') || ' (V' ||
v_Version || ')');
dbms_output.put_line('---------------------------------------------');
for v_Database in (SELECT NAME FROM V$DATABASE@NOETIX_TEST) loop
v_RemoteDB := v_Database.name;
dbms_output.put_line('(Remote) Database = ' || v_Database.name);
end loop;
for v_Database in (SELECT NAME FROM V$DATABASE) loop
v_LocalDB := v_Database.name;
dbms_output.put_line('(Local) Database = ' || v_Database.name);
end loop;
dbms_output.put_line(' ');
dbms_output.put_line('SECTION 001- CHECKING VIEWS');
dbms_output.put_line('---------------------------');
if v_ShowDangerItemsOnly = 'F' then
dbms_output.put_line('New Views');
dbms_output.put_line('---------');
v_ChangeCount := 0;
for v_LocalView in (SELECT DISTINCT nv.View_Name
FROM N_Views NV
WHERE EXISTS
(SELECT 'X'
FROM All_Views AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
ORDER BY nv.View_Name) loop
SELECT COUNT(*)
INTO v_ViewCount
FROM N_Views@NOETIX_TEST NV
WHERE EXISTS (SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
AND nv.view_name = v_LocalView.view_name;
if v_ViewCount = 0 then
if v_ShowDetails = 'T' then
dbms_output.put_line(v_LocalView.view_name);
end if;
v_ChangeCount := v_ChangeCount + 1;
end if;
end loop;
dbms_output.put_line('-- Total New View Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
end if;
dbms_output.put_line('Removed Views');
dbms_output.put_line('-------------');
v_ChangeCount := 0;
for v_RemoteView in (SELECT DISTINCT nv.View_Name
FROM N_Views@NOETIX_TEST NV
WHERE EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
ORDER BY nv.View_Name) loop
SELECT COUNT(*)
INTO v_ViewCount
FROM N_Views NV
WHERE EXISTS (SELECT 'X'
FROM All_Views AV
WHERE av.Owner = USER
AND av.view_name = UPPER(nv.View_name))
AND nv.view_name = v_RemoteView.view_name;
if v_ViewCount = 0 then
if v_ShowDetails = 'T' then
dbms_output.put_line(v_RemoteView.view_name);
end if;
v_ChangeCount := v_ChangeCount + 1;
end if;
end loop;
dbms_output.put_line('-- Total Removed View Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
dbms_output.put_line('SECTION 002- CHECKING COLUMNS');
dbms_output.put_line('-----------------------------');
dbms_output.put_line('Views With Column Changes');
dbms_output.put_line('-------------------------');
v_ChangeCount := 0;
v_OldViewname := '@';
for v_Column in (select atc.table_name view_name,
'ADDED' change_type,
atc.column_name
from all_tab_columns ATC
where atc.owner = user
and atc.COLUMN_NAME NOT LIKE 'A$%'
and atc.COLUMN_NAME <> 'Z$$_________________________'
and atc.TABLE_NAME IN
(SELECT UPPER(nv.view_name)
FROM N_Views@NOETIX_TEST nv
WHERE NVL(nv.omit_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 'X'
FROM all_tab_columns@NOETIX_TEST ATC1
WHERE ATC.OWNER = ATC1.OWNER
AND ATC.TABLE_NAME = ATC1.TABLE_Name
AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
AND v_ShowDangerItemsOnly = 'F'
UNION
select atc.table_name view_name,
'REMOVED',
atc.column_name
from all_tab_columns@NOETIX_TEST ATC
where atc.owner = user
and atc.COLUMN_NAME NOT LIKE 'A$%'
and atc.COLUMN_NAME <> 'Z$$_________________________'
and atc.TABLE_NAME IN
(SELECT UPPER(nv.view_name)
FROM N_Views nv
WHERE NVL(nv.omit_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 'X'
FROM all_tab_columns ATC1
WHERE ATC.OWNER = ATC1.OWNER
AND ATC.TABLE_NAME = ATC1.TABLE_Name
AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
ORDER BY 1, 2, 3) loop
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_Column.view_name then
v_OldViewname := v_Column.view_name;
dbms_output.put_line(v_Column.view_name);
end if;
if v_ShowDetails = 'T' then
dbms_output.put_line(' ' || INITCAP(v_Column.Change_Type) || ' ' ||
v_Column.Column_Name);
end if;
end loop;
dbms_output.put_line('-- Total Column Changes Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
dbms_output.put_line('Columns With Modified Source');
dbms_output.put_line('----------------------------');
dbms_output.put_line('NOTE: This is a comparison of the Noetix tables, not the view source themselves');
v_ChangeCount := 0;
v_OldViewname := '@';
for v_Column in (SELECT nv.View_Name Test_View_Name,
nv.query_position Test_Query_Position,
nv.column_name Test_column_name,
nv.column_type Test_Column_Type,
NVCT.last_updated_by Test_Last_Updated_By,
NVCT.Last_update_Date Test_Last_Udpated_Date,
NVL(nv.table_alias, '') || '.' ||
Replace(Replace(Replace(nv.column_expression,
' ',
' '),
chr(13),
''),
chr(10),
'') Test_Column_Detail,
nv1.View_Name Prod_View_Name,
nv1.query_position Prod_Query_Position,
nv1.column_name Prod_column_name,
nv1.column_type Prod_Column_Type,
NVCT1.last_updated_by Prod_Last_Updated_By,
NVCT1.Last_update_Date Prod_Last_Udpated_Date,
NVL(nv1.table_alias, '') || '.' ||
Replace(Replace(Replace(nv1.column_expression,
' ',
' '),
chr(13),
''),
chr(10),
'') Prod_Column_Detail,
Replace(Replace(Replace(nv1.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'') || ' > ' ||
Replace(Replace(Replace(nv.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'') Change_Pattern
FROM N_View_Columns NV
JOIN N_View_Column_Templates NVCT
ON NV.T_COLUMN_ID = NVCT.t_Column_Id
JOIN n_view_columns@NOETIX_TEST NV1
ON nv1.view_name = nv.view_name
AND nv1.column_name = nv.column_name
AND nv1.query_position = nv.query_position
AND UPPER(nv1.View_name) IN
(SELECT AV.VIEW_NAME
FROM All_Views AV
WHERE AV.Owner = USER)
AND NVL(NV1.OMIT_FLAG, 'N') = 'N'
AND NVL(nv1.column_expression, 'NULL') NOT LIKE
'%Copyright Noetix Corporation%'
JOIN N_View_Column_Templates@NOETIX_TEST NVCT1
ON NV1.column_label = nvct1.column_label
AND nv1.view_label = nvct1.view_label
AND nv1.query_position = nvct1.query_position
WHERE 1 = 1
AND UPPER(NV.View_Name) NOT LIKE '%_BASE'
AND nv.column_type NOT IN ('GENEXPR')
AND NVL(NV.OMIT_FLAG, 'N') = 'N'
AND NVL(nv.column_expression, 'NULL') NOT LIKE
'%Copyright Noetix Corporation%'
AND UPPER(nv.View_name) IN
(SELECT AV.VIEW_NAME
FROM All_Views@NOETIX_TEST AV
WHERE AV.Owner = USER)
AND NVL(lower(CASE
WHEN nv1.COLUMN_TYPE = 'EXPR' THEN
''
ELSE
NVL(nv1.table_alias, '') || '.'
END || Replace(Replace(Replace(nv1.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'')),
'NULL') <> NVL(lower(CASE
WHEN nv.COLUMN_TYPE = 'EXPR' THEN
''
ELSE
NVL(nv.table_alias, '') || '.'
END || Replace(Replace(Replace(nv.column_expression,
' ',
''),
chr(13),
''),
chr(10),
'')),
'NULL')
ORDER BY nv.View_Name, nv.query_position, nv.column_name) loop
--if v_Column.Test_Column_Type = v_Column.Prod_Column_Type then
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_Column.Test_View_Name then
v_OldViewname := v_Column.Test_View_Name;
dbms_output.put_line('===== ' || UPPER(v_Column.Test_View_Name) ||
' =====');
end if;
dbms_output.put_line(' ' || v_Column.Test_Column_Name || ' (' ||
v_Column.Test_Column_Type || ' Query Position ' ||
v_Column.Test_Query_Position || ')');
if v_ShowDetails = 'T' then
dbms_output.put_line(' ' || v_LocalDB || '=' ||
v_Column.Test_Column_Detail || ' (' ||
v_Column.Test_Last_updated_By || ', ' ||
TO_CHAR(v_Column.Test_Last_Udpated_Date,
'DD-MON-YYYY') || ')');
dbms_output.put_line(' ' || v_RemoteDB || '=' ||
v_Column.Prod_Column_Detail || ' (' ||
v_Column.Prod_Last_updated_By || ', ' ||
TO_CHAR(v_Column.Prod_Last_Udpated_Date,
'DD-MON-YYYY') || ')');
end if;
--end if;
end loop;
dbms_output.put_line('-- Total Modified Column Count = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line('');
dbms_output.put_line('SECTION 003- CHECKING VIEW COMPOSITION');
dbms_output.put_line('--------------------------------------');
dbms_output.put_line('Changed Where Clause Conditions');
dbms_output.put_line('------------------------------');
v_ChangeCount := 0;
for v_WhereChange in (select 'Added' change_direction,
NVW.view_name,
nvw.query_position,
nvw.where_clause_position,
nvw.where_clause,
nvwt.last_updated_by,
nvwt.last_update_date
FROM N_View_Wheres NVW
JOIN n_View_Where_Templates NVWT
ON NVW.View_Label = NVWT.View_Label
AND nvw.query_position = nvwt.query_position
AND nvw.where_clause_position =
nvwt.where_clause_position
JOIN All_Views AV
ON AV.owner = USER
AND AV.VIEW_NAME = UPPER(NVW.view_name)
WHERE 1 = 1
AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
AND NVL(NVW.Omit_Flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'X'
FROM N_View_Wheres@NOETIX_TEST NVW_R
WHERE NVW.VIEW_NAME = NVW_R.view_name
AND NVW.query_position =
NVW_R.query_position
AND NVW.where_clause = NVW_R.where_clause)
AND EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV1
WHERE AV1.owner = USER
AND AV1.VIEW_NAME = UPPER(NVW.view_name))
UNION
SELECT 'Removed',
NVW.view_name,
nvw.query_position,
nvw.where_clause_position,
nvw.where_clause,
nvwt.last_updated_by,
nvwt.last_update_date
FROM N_View_Wheres@NOETIX_TEST NVW
JOIN n_View_Where_Templates@NOETIX_TEST NVWT
ON NVW.View_Label = NVWT.View_Label
AND nvw.query_position = nvwt.query_position
AND nvw.where_clause_position =
nvwt.where_clause_position
WHERE 1 = 1
AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
AND NVL(NVW.Omit_Flag, 'N') = 'N'
AND EXISTS
(SELECT 'X'
FROM All_Views@NOETIX_TEST AV
WHERE AV.owner = USER
AND AV.VIEW_NAME = UPPER(NVW.view_name))
AND NOT EXISTS
(SELECT 'X'
FROM N_View_Wheres NVW_R
WHERE NVW.VIEW_NAME = NVW_R.view_name
AND NVW.query_position =
NVW_R.query_position
AND NVW.where_clause = NVW_R.where_clause)
AND EXISTS
(SELECT 'X'
FROM All_Views AV1
WHERE AV1.owner = USER
AND AV1.VIEW_NAME = UPPER(NVW.view_name))
ORDER BY 2, 3, 4, 5) loop
v_ChangeCount := v_ChangeCount + 1;
if v_OldViewname <> v_WhereChange.View_Name then
v_OldViewname := v_WhereChange.View_Name;
dbms_output.put_line('===== ' || UPPER(v_WhereChange.View_Name) ||
' =====');
end if;
if v_ShowDetails = 'T' then
dbms_output.put_line(' Query Position ' ||
v_WhereChange.Query_Position || ' ' ||
InitCap(v_WhereChange.Change_Direction) || ': ' ||
v_WhereChange.Where_Clause || ' (Position = ' ||
TO_CHAR(v_WhereChange.Where_Clause_Position) || ', ' ||
v_WhereChange.Last_updated_By || ', ' ||
TO_CHAR(v_WhereChange.Last_Update_Date,
'DD-MON-YYYY') || ')');
end if;
end loop;
dbms_output.put_line('-- Total Where Clause Changes = ' ||
TO_CHAR(v_ChangeCount));
dbms_output.put_line(' ');
rollback;
end;
This code is available here (via Google Docs).
NOTE: I've updated the SQL to do some additional comparisons and format the comparisons completely differently.
0 comments:
Post a Comment