Noetix: Extracting a View As A Script

UPDATE 16-DEC-2012: Added in the table n_view_col_property_templates. Where a view is using key flex fields the copying of the view was failing as the additional information (well, for Inventory anyway) was not being populated in this table.

The following script generates source code (i.e. uses DBMS_OUTPUT.PUT_LINE), you will need to copy/paste this into a separate file AND then change the name of the view before you run it (otherwise you will get a lot of duplicate errors).

Five fields are overwritten by this script; Last Updated By/Created By (replaced with the value in the variable at the top), Last Updated Date/Creation Date (set to today), and the product version set to 12+.

To add other tables you can just add calls to "ProcessTable" (rows 91-96) for the additional tables you want to include.

The script is available here (via Google Docs) or is copy/ pasted below;

declare
  v_ViewLabel     n_view_column_templates.view_label%TYPE := 'GMD_Recipes'; -- Must be direct value from table n_views, case sensitive!
  v_LastUpdatedBy n_view_templates.last_updated_by%TYPE := 'A Pellew'; -- The user who performed the last update (i.e. you)

  procedure ProcessTable(v_TableName in varchar2) as
    TYPE rowidRec IS RECORD(
      ri rowid);
    TYPE rowidSet IS TABLE OF rowidRec;
    v_Items rowidSet;

    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('-- Performing updates to table ' ||
                         upper(v_tablename));
    EXECUTE IMMEDIATE 'SELECT n.rowid FROM ' || v_TableName ||
                      ' n WHERE n.view_label = ''' || v_ViewLabel || '''' BULK
                      COLLECT
      INTO v_Items;
    for v_Item in v_Items.First .. v_items.Last loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO ' || Lower(v_TableName) || ' (');
      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 = Upper(v_TableName)
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name || ' FROM ' ||
                 Upper(v_TableName) || ' T WHERE T.ROWID = ''' || v_Items(v_Item).ri || '''';
        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 in ('LAST_UPDATE_DATE', 'CREATION_DATE') then
          v_result := SYSDATE;
        end if;
        if v_Column.Column_Name IN ('LAST_UPDATED_BY', 'CREATED_BY') then
          v_result := v_LastUpdatedBy;
        end if;
        if v_Column.Column_Name IN ('T_COLUMN_PROPERTY_ID') then
          v_result := '(SELECT MAX(t_column_property_id)+1 FROM n_view_col_property_templates)';
        end if;
    
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            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;
            AddToheader(lower(V_Column.Column_name) || ')');
          else
            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;
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || 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(';');
      dbms_output.put_line('');
    end loop;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('SET SCAN OFF');
  dbms_output.put_line('');
  ProcessTable('n_view_templates');
  ProcessTable('n_role_view_templates');
  ProcessTable('n_view_query_templates');
  ProcessTable('n_view_table_templates');
  ProcessTable('n_view_where_templates');
  ProcessTable('n_view_column_templates');
  ProcessTable('n_view_col_property_templates');

  dbms_output.put_line('COMMIT;');  dbms_output.put_line('');  dbms_output.put_line('SET SCAN ON');  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');
end;