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.

Read More

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;

Read More