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:
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!
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!
