Noetix: Comparing NOETIX_SYS Schemas

As I'm currently working on upgrading our Noetix installation from 6.0.2 to 6.1 I've been writing some SQL to do a comparison between our new 6.1 build (on a test server) and our current production 6.1 environment. At the moment the only things I'm comparing are the views (looking for new and removed), and columns looking for new and removed and where the source code has changed.

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;

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.