Oracle PL/SQL: Using Dynamic SQL to Build an INSERT ... INTO Statement From Any Query

At the moment I'm writing test script for an Oracle Internet Expenses implementation. A fairly simple need has arisen to take the result of a SELECT ... FROM statement and convert it into an INSERT ... INTO - basically this will allow some of the tests to be repeatable (i.e. they are creating users, assigning responsibilities, etc).

The following PL/SQL script generates insert statements using the standard DBMS_OUTPUT package:

declare
  -- Script to convert a SQL Statement into an INSERT statement 
  -- (useful for generating test scripts)

  v_Spacing     varchar2(10) := '  '; -- used to split "levels" in SQL
  v_Table       all_tab_cols.table_name%TYPE := upper('wf_local_user_roles');
  v_Owner       all_tab_cols.owner%TYPE := upper('APPLSYS');
  v_WhereClause varchar2(2048) := 'where user_name = '''' and role_orig_system_id = 22918';
  v_QuerySQL    varchar2(2048);
  v_Result      varchar2(512);
  v_RowID       ROWID;

  v_ColumnCount number := 0;

  TYPE ref_cur_typ IS REF CURSOR;
  ref_cur  ref_cur_typ;
  data_cur ref_cur_typ;

  cursor c_Columns is
    select atc.column_name, atc.data_type
      from all_tab_cols atc
     where atc.owner = v_Owner
       and atc.table_name = v_Table
     order by atc.column_id;
begin
  v_QuerySQL := 'select ROWID from ' || v_Owner || '.' || v_Table || ' ' ||
                v_WhereClause;

  open ref_cur for v_QuerySQL;
  loop
    -- Get the ROW ID (unique identifier) for each row we wish to add as an insert
    fetch ref_cur
      into v_RowID;
    EXIT WHEN ref_cur%NOTFOUND;
    dbms_output.put_line(v_Spacing || 'insert into ' || v_Owner || '.' ||
                         v_Table);
    dbms_output.put_line(v_Spacing || 'select');
    v_ColumnCount := 0;
    for v_Column in c_Columns loop
      -- Loop through the columns in the table, for each row
      v_ColumnCount := v_ColumnCount + 1;
      if v_Column.data_type in ('VARCHAR2', 'CHAR') then
        v_QuerySQL := 'select ' || v_Column.Column_Name || ' from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('''' || v_Result || '''');
      elsif v_Column.data_type in ('FLOAT', 'NUMBER') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line(v_Result);
      elsif v_Column.data_type in ('DATE') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ', ''DD-MON-YYYY HH24:MI:SS'') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('to_date(''' || v_Result || ''', ''DD-MON-YYYY HH24:MI:SS'')');
      end if;
    end loop;
    dbms_output.put_line(v_Spacing || 'from dual;');
  end loop;
  close ref_cur;
end;

This will only handle tables where all the columns are of one of the specified data types (DATE, VARCHAR2, NUMBER, etc). The resulting insert statements are written to the standard output channel so if you have a lot of records you might want to enlarge it beyond the 10,000 character (or so) default!