Noetix: Extracting All Changes To a NoetixView To A Single File

NOTE: Last update, adding in some more information to the report, new version 0.9.2. It's now a requirement that you use this script to Omit Columns (makes the SQL much more readable, and reduces the amount of code that needs to be updated when Noetix update their processes - as they did at 6.0.2).

The purpose of this script is to combine all the changes made by a specific group of users to a single NoetixView template (in the tables N_View_Column_Templates, N_View_Table_Templates, and N_View_Where_Templates) into a single file split into four sections according to the template below;

-- NOETIX VIEW UPDATE TEMPLATE V0.9.1
-- NOTE: All changes to a single Noetix view should be included in one file (base view changes
--   should be in a separate file)
--
@utlspon

-- Section 1: Removing existing columns, queries, etc use a single update where possible

-- Section 2: Table (and Where clause) additions. Group by the change, not the type (i.e. add the
--   first table, then add the where clauses for the first table, then the second table, then the
--   where clauses for the second table, etc)

-- Section 3: Column Additions (in alphabetical order of Column_Label)

-- Section 4: Updates

COMMIT;

@utlspoff
 
The script is available here (via Google Docs), because it's quite long I've not copied/ pasted it into this blog.

Just to give you some idea we have approximately 3,000 files, the aim of using this script is to reduce this to a more manageable number (in the ten's).

The newly announced NoetixViews Workbench will (hopefully) further reduce the need for these legacy files.

Noetix: Generating a (XU2) Column-Addition Script

In order to use the code below you need to replace the values for v_ViewLabel and v_ColumnLabel in the block with values that are relevant to the column you are trying to add. This script uses the standard DBMS_OUTPUT package to output the script - depending on the nature of the column you're copying this could be quite big.

If you want to include new tables in your column addition script then set the v_IncludeWhere clause to Y (otherwise only the new column script will be generated).

The script is below;

declare
  v_ViewLabel    n_view_column_templates.view_label%TYPE := 'GMF_Order_Details_Base'; -- Must be direct value from table, case sensitive!
  v_ColumnLabel  n_view_column_templates.column_label%TYPE := 'Location';
  v_IncludeWhere varchar2(1) := 'N'; -- Must be either Y or N
  v_ColumnCount  number := 1;

  v_HeaderSQL varchar2(4000);
  v_DataSQL   varchar2(4000);
  v_SQL       varchar2(4000);
  v_result    varchar2(4000);

  procedure AddToHeader(v_Text in varchar) as
  begin
    if length(v_HeaderSQL) > 100 then
      dbms_output.put_line(v_headerSQL);
      v_headerSQL := '  ';
    end if;
    v_headerSQL := v_headerSQL || v_Text;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_' ||
                       lower(v_ColumnLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('COLUMN max_col NEW_VALUE max_col_position');
  dbms_output.put_line('');
  dbms_output.put_line('SELECT MAX(column_position) max_col');
  dbms_output.put_line('FROM   n_view_column_templates');
  dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
  dbms_output.put_line('');

  if (v_IncludeWhere = 'Y') then
    dbms_output.put_line('COLUMN max_where NEW_VALUE max_where_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(where_clause_position) max_where');
    dbms_output.put_line('FROM   n_view_where_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
    dbms_output.put_line('COLUMN max_from NEW_VALUE max_from_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(from_clause_position) max_from');
    dbms_output.put_line('FROM   n_view_table_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
 
    for v_Data in (select n.*, n.rowid
                     from n_view_table_templates n
                    where n.view_label = v_ViewLabel
                      and n.table_alias in
                          (select n.table_alias
                             from n_view_column_templates n
                            where n.view_label = v_ViewLabel
                              and n.column_label = v_ColumnLabel)) loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_table_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG',
                                       'GEN_SEARCH_BY_COL_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_TABLE_TEMPLATES'
                          AND atc.column_name not in
                              ('INCLUDE_FLAG', 'GEN_SEARCH_BY_COL_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_TABLE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'FROM_CLAUSE_POSITION' then
          v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
 
    v_ColumnCount := 1;
    for v_Data in (select n.*, n.rowid
                     from n_view_where_templates n
                    where n.view_label = v_ViewLabel
                      and n.where_clause like
                          '%' ||
                          (select q.table_alias
                             from n_view_column_templates q
                            where q.view_label = v_ViewLabel
                              and q.column_label = v_ColumnLabel
                              and q.QUERY_POSITION  = n.QUERY_POSITION) || '%') loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_where_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_WHERE_TEMPLATES'
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_WHERE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'WHERE_CLAUSE_POSITION' then
          v_result := '(&max_where_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
  end if;
  v_ColumnCount := 1;
  for v_Data in (select n.*, n.rowid
                   from n_view_column_templates n
                  where n.view_label = v_ViewLabel
                    and lower(n.column_label) = lower(v_ColumnLabel)) loop
    v_HeaderSQL := '  ';
    v_DataSQL   := '';
    dbms_output.put_line('INSERT INTO n_view_column_templates(');
    for v_Column in (select atc.column_name,
                            atc.data_type,
                            atc.column_id,
                            (select max(atc2.column_id)
                               from all_tab_columns atc2
                              where atc2.owner = USER
                                AND atc2.column_name not in ('INCLUDE_FLAG')
                                and atc2.table_name = atc.table_name) as max_column_id
                       from all_tab_columns atc
                      where atc.owner = USER
                        and atc.table_name = 'N_VIEW_COLUMN_TEMPLATES'
                        AND atc.column_name not in ('INCLUDE_FLAG')
                      order by atc.column_id) loop
      v_SQL := 'SELECT T.' || V_Column.Column_name ||
               ' FROM N_VIEW_COLUMN_TEMPLATES T WHERE T.ROWID = ''' ||
               v_Data.rowid || '''';
      EXECUTE IMMEDIATE v_SQL
        into v_result;
      if instr(v_Result, '''') > 0 then
        v_Result := Replace(v_result, '''', '''''');
      end if;
      if v_Column.Column_Name = 'COLUMN_POSITION' then
        v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
      end if;
      if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
        v_result := SYSDATE;
      end if;
   
      if (v_result is not null) then
        if (v_Column.Column_Id = v_Column.Max_Column_Id) then
          AddToheader(lower(V_Column.Column_name) || ')');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''') -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || ''')) -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end;
        else
          AddToHeader(lower(V_Column.Column_name) || ', ');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''', -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || '''), -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end || chr(13);
        end if;
      end if;
    end loop;
    dbms_output.put_line(v_headerSQL);
    dbms_output.put_line('VALUES(');
    dbms_output.put_line(v_DataSQL);
    dbms_output.put_line(';');
 
    v_ColumnCount := v_ColumnCount + 1;
    dbms_output.put_line('');
  end loop;

  dbms_output.put_line('COMMIT;');
  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');

end;