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.

Noetix: Removing Invalid Views (In A Supported Way)

This blog post covers use of a fairly simple script to drop any invalid views in the NOETIX_SYS schema that should not have been already removed by the regenerate process (i.e. where a view shouldn't be there at all rather than having just failed to build correctly).

The script (callable from XU6) is;

@utlspon xu6_drop_unused_invalid_views_upd

begin
  dbms_output.put_line('See 110930-000002 Invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'DROP VIEW NOETIX_SYS.' || do.object_name text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/

commit;

@utlspoff
NOTE: If you have not called your Noetix schema owner NOETIX_SYS you will need to alter all occurrences of that in the above SQL!

Noetix: Identifying Un-used Columns For Removal

This blog post covers a description of an application (source code provided) that identifies columns which are not required (i.e. all values of the column in the view are the same) and generates a script that somone who has completed the NoetixViews Customization Certification course can include in the Generation Scripts.


The first thing to note is how this works; it opens every view doing a MIN/MAX on every column. It has been tested on a 16GB database and works fine - of course the main requirement of an application like this is to have enough TEMP tablespace in order to work. This is the reason the columns are dealt with individually rather than just opening each view and doing a MIN/MAX on every column in the view in a single hit - this was blowing out the 5GB of TEMP tablespace we had available on the test system.

The application is a simple console application:

All it does is process the views in the database using the SQL:

SELECT DISTINCT UPPER(v.VIEW_NAME) VIEWNAME
  FROM n_views v
 WHERE EXISTS
 (SELECT 'x'
          FROM all_views av
         WHERE av.OWNER = USER
           AND av.VIEW_NAME = UPPER(v.VIEW_NAME))
   AND NVL(v.OMIT_FLAG, 'N') = 'N'
   AND NVL(v.Special_Process_Code, 'X') NOT IN ('LOV', 'BASEVIEW')
   AND (UPPER(v.VIEW_NAME) = '%VIEWNAME%' OR '%VIEWNAME%' = 'ALL')
ORDER BY UPPER(v.VIEW_NAME)

This gives a list of all the views in the database. The %VIEWNAME% is replaced with the first argument on the command line (optional). If nothing is specified then it is replaced with ALL.

Views which are omitted by default are automatically excluded as are all views which are of type LOV (list of values) or BASEVIEW.

Once the list of views has been obtained then the list of columns in each view is retrieved using the SQL:

 SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
  FROM ALL_TAB_COLUMNS ATC
 WHERE ATC.OWNER = USER
   AND ATC.TABLE_NAME = '%VIEWNAME%'
   AND ATC.COLUMN_NAME NOT LIKE 'A$%'
   AND ATC.COLUMN_NAME NOT LIKE 'Z$%'
   AND ATC.DATA_TYPE NOT IN ('ROWID', 'LONG')
   AND INSTR(ATC.COLUMN_NAME, '$') = 0

The list of  columns excludes the A$ and Z$ columns that are managed by Noetix and columns which contain a "$" symbol (flexfields) as if you want to remove these you can disable them in the e-Business Suite itself.

Once the appliction has the list of columns it looks through them building the SQL:

SELECT 1 AS IGNORE
   ,TO_CHAR(MIN()) AS MIN
   ,TO_CHAR(MAX()) AS MAX
FROM

The MIN and MAX values are then compared, if they are the same when an SQL file is created for the suppression of the field (for future reference the MIN and MAX values returned are stored in the file).

As you can imagine when accessing the GL views this query can take quite a while - for our 16GB database processing all the views in our relatively simple configuration will take about a week (running multiple processes has gotten this down to 2-3 days).

This isn't quick - but it is effective - we are removing around 3,000 columns from the views. That's 3,000 less columns in the help and for our end users to "accidentally" pick the wrong one from!

Source Code: here (Visual Studio 2010, 20kb Zip Archive)
Build Environment: VS2010 + Oracle Client