Oracleテーブルのデータをエクスポートしてinsert intoスクリプトを生成します。

create or replace procedure print_insert(v_tname varchar2,v_cbatch number default 0)  
/*  
v_tname    sql      [email protected]  
v_cbatch   commit     :http://blog.itpub.net/xzh2000  
*/  
as  
/*          */  
type cur_alldata is ref cursor;  
l_alldata cur_alldata;  
/*        v_row*/  
v_sql varchar2(3999);  
v_row varchar2(3999);  
/*         */  
function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2;  
/*         */  
function formatdata(v_tname varchar2,v_row varchar2) return varchar2  
as  
v_ldata varchar2(32765);  
v_rdata varchar2(32765);  
v_cname varchar2(3999);  
v_instr number(10);  
v_count number(6);  
begin  
v_instr := instr(v_row,'(');  
v_ldata := substr(v_row,1,v_instr);  
v_rdata := substr(v_row,v_instr+1);  
v_instr := instr(v_rdata,')');  
v_rdata := substr(v_rdata,1,v_instr-1);  
  
v_count := 0;  
loop  
v_instr := instr(v_rdata,',');  
exit when v_instr = 0;  
  
v_cname := substr(v_rdata,1,v_instr-1);  
v_rdata := substr(v_rdata,v_instr+1);  
v_count := v_count + 1;  
/*            */  
v_cname := formatfield(v_tname,v_cname,v_count);  
  
/*           v_ldata */  
if v_count = 1 then  
v_ldata := v_ldata||v_cname;  
else  
v_ldata := v_ldata||','||v_cname;  
end if;  
  
end loop;  
  
/*            */  
if v_count = 1 then  
v_ldata := v_ldata||formatfield(v_tname,v_rdata,v_count+1)||');';  
else  
v_ldata := v_ldata||','||formatfield(v_tname,v_rdata,v_count+1)||');';  
end if;  
return v_ldata;  
end;  
/*               */  
function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2  
as  
v_name varchar2(3999);  
v_type varchar2(99);  
begin  
select coltype into v_type from col where tname = upper(v_tname) and colno = v_colno;  
if v_type = 'DATE' then  
v_name := 'to_date('||''''||v_cname||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';  
elsif v_type = 'VARCHAR2' then  
v_name := ''''||v_cname||'''';  
else  
v_name := v_cname;  
end if;  
return v_name;  
end;  
/*           */  
function getfields(v_tname varchar2) return varchar2  
as  
v_fields varchar2(3999);  
begin  
for cur_fname in (select cname,coltype from col where tname = upper(v_tname) order by colno) loop  
if v_fields is null then  
v_fields := 'nvl('||cur_fname.cname||','||''''||'0'||''''||')';  
else  
v_fields := v_fields||'||'',''||'||'nvl('||cur_fname.cname||','||''''||'0'||''''||')';  
end if;  
end loop;  
v_fields := 'select '||''''||'insert into '||v_tname||' values('||''''||'||'||v_fields||'||'||''''||')'||''''||' from '||v_tname;  
return v_fields;  
end;  
begin  
execute immediate 'alter session set nls_date_format='||''''||'yyyy-mm-dd hh24:mi:ss'||'''';  
dbms_output.put_line(' *** XX    ！ *** ');  
  
v_sql := getfields(v_tname);  
--dbms_output.put_line(v_sql);  
open l_alldata for v_sql;  
loop  
fetch l_alldata into v_row;  
exit when l_alldata%notfound;  
--dbms_output.put_line(v_row);  
  
dbms_output.put_line(formatdata(v_tname,v_row));  
if mod(l_alldata%rowcount,v_cbatch) = 0 then  
dbms_output.put_line('commit;');  
end if;  
end loop;  
  
close l_alldata;  
end;