Generate Migration Sql
Example
begin dbms_output.put_line(osp_std.tpl_insert('use%')); end; /
create or replace package osp_std is function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2; function tpl_insert(p_table_name varchar2) return varchar2; end; / create or replace package body osp_std is function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result:= l_result || p_del; end if; l_result :=l_result || l_value; end loop; return l_result; end join; /** * generate insert template for table * usage * begin dbms_output.put_line(osp_std.tpl_insert('m002%')); end; * / */ function tpl_insert(p_table_name varchar2) return varchar2 is l_rst varchar2(32767); l_table_name user_tables.table_name%type; begin select table_name into l_table_name from user_tables where table_name like upper(p_table_name) order by table_name; select 'insert into ' || l_table_name || '(' || chr(10) || osp_std.join(cursor( select column_name from user_tab_columns tc where table_name = l_table_name order by tc.table_name, tc.column_id), ',' || chr(10)) || chr(10) || ') values (' || chr(10) || '-- ' || osp_std.join(cursor( select column_name from user_tab_columns tc where table_name = l_table_name order by tc.table_name, tc.column_id), chr(10) || ', ' || chr(10) || '-- ') || chr(10) || chr(10) || ');' into l_rst from dual; return l_rst; end; end; /