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;
/