set echo off serveroutput on feedback off trimspool on line 10000 verify off head off
set head on line 100 feedback on termout on echo on
set serverout put on echo off feedback off trimspool on line 1000 verify off feedback off head off termout off
define tab_name='&1'spool ./data/&tab_name..sql
declare
cursor c1(v_owner varchar2,v_tab_name varchar2) is
select column_name,data_type
from all_tab_columns
where table_name = v_tab_name
and owner = v_tab_name;
field_list varchar2(4000);
field_list_sel varchar2(4000);
fmt_field varchar2(200);
date_format varchar2(20):='yyyymmddhh24miss';
v_owner varchar2(30) :=user;
v_tab_name varchar2(30) :=upper(trim('&tab_name'));
begin
dbms_output.enable(1000000);
dbms_output.put_line('set echo off feedback off');
dbms_output.put_line('spool ./data/&tab_name' || '_data.sql'); dbms_output.put_line('variable field_list varchar2(4000);');
dbms_output.put_line('begin');
dbms_output.put_line(' :field_list:=''insert into &tab_name('''); for rec1 in c1(v_owner,v_tab_name) loop
if c1%rowcount=1 then
field_list := ' || '' ' || rec1.column_name || '''';
else
field_list :=' || '',' || rec1.column_name || '''';
end if;
dbms_output.put_line(field_list);
dbms_output.put_line(''||')'|| chr(10)||'values('||';');
dbms_output.put_line('end;');
dbms_output.put_line('/');
dbms_output.put_line('select :field_list || chr(10) ||'); for rec1 in c1(v_owner,v_tab_name)
loop
if rec1.data_type='DATE' then
fmt_field:='''to_date(''''''||to_char('|| rec1.column_name||','''|| date_format ||''')||'''''','''''|| date_format||''''')''';
elsif rec1.data_type in ('CHAR','VARCHAR2') then
fmt_field:='''''''''|| replace('|| rec1.column_name || ','''''''','''''''''''')||''''''''';
else
fmt_field:='to_char('|| rec1.column_name || ')';
end if;
if c1%rowcount=1 then
field_list_sel:=fmt_field;
else
field_list_sel:='||'',''||' || fmt_field;
end if ;
dbms_output.put_line(field_list_sel);
end loop; dbms_output.put_line('''||''),''');
dbms_output.put_line('from &tab_name ;');
dbms_output.put_line('spoo off;');
end;
set head on line 100 feedback on termout on echo on
set serverout put on echo off feedback off trimspool on line 1000 verify off feedback off head off termout off
define tab_name='&1'spool ./data/&tab_name..sql
declare
cursor c1(v_owner varchar2,v_tab_name varchar2) is
select column_name,data_type
from all_tab_columns
where table_name = v_tab_name
and owner = v_tab_name;
field_list varchar2(4000);
field_list_sel varchar2(4000);
fmt_field varchar2(200);
date_format varchar2(20):='yyyymmddhh24miss';
v_owner varchar2(30) :=user;
v_tab_name varchar2(30) :=upper(trim('&tab_name'));
begin
dbms_output.enable(1000000);
dbms_output.put_line('set echo off feedback off');
dbms_output.put_line('spool ./data/&tab_name' || '_data.sql'); dbms_output.put_line('variable field_list varchar2(4000);');
dbms_output.put_line('begin');
dbms_output.put_line(' :field_list:=''insert into &tab_name('''); for rec1 in c1(v_owner,v_tab_name) loop
if c1%rowcount=1 then
field_list := ' || '' ' || rec1.column_name || '''';
else
field_list :=' || '',' || rec1.column_name || '''';
end if;
dbms_output.put_line(field_list);
dbms_output.put_line(''||')'|| chr(10)||'values('||';');
dbms_output.put_line('end;');
dbms_output.put_line('/');
dbms_output.put_line('select :field_list || chr(10) ||'); for rec1 in c1(v_owner,v_tab_name)
loop
if rec1.data_type='DATE' then
fmt_field:='''to_date(''''''||to_char('|| rec1.column_name||','''|| date_format ||''')||'''''','''''|| date_format||''''')''';
elsif rec1.data_type in ('CHAR','VARCHAR2') then
fmt_field:='''''''''|| replace('|| rec1.column_name || ','''''''','''''''''''')||''''''''';
else
fmt_field:='to_char('|| rec1.column_name || ')';
end if;
if c1%rowcount=1 then
field_list_sel:=fmt_field;
else
field_list_sel:='||'',''||' || fmt_field;
end if ;
dbms_output.put_line(field_list_sel);
end loop; dbms_output.put_line('''||''),''');
dbms_output.put_line('from &tab_name ;');
dbms_output.put_line('spoo off;');
end;
and owner = v_tab_name;
里的OWNER应该是: owner = v_ower吧