p_gettabdata
create or replace procedure p_gettabdata(i_owner in varchar2,i_table in varchar2)
is
v_insrt varchar2(4000);
v_column_row varchar2(4000);
v_mx_col_id number;
v_owner varchar2(30);
v_table varchar2(30);
cursor cur_f is
select atc.COLUMN_NAME,atc.DATA_TYPE,atc.CHAR_LENGTH,atc.COLUMN_ID,atc.num_nulls,atc.data_length from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table order by atc.COLUMN_ID;
v_column_name varchar2(4000);
type curtype is ref cursor;
cur_data curtype;
fhandle utl_file.file_type;
v_sql varchar2(4000);
v_del varchar2(4000);
--v_mac varchar2(64);
v_show clob;
begin
v_owner := upper(trim(i_owner));
v_table := upper(trim(i_table));
v_column_name := '';
select wm_concat(atcs.COLUMN_NAME) into v_column_row from (select atc.column_name,atc.column_id from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table order by atc.column_id) atcs;
--select MACHINE into v_mac from v$session where username=v_owner;
v_insrt := 'insert into '||v_owner||'.'||v_table||'('||v_column_row||')'||' values(';
select max(atc.COLUMN_ID) into v_mx_col_id from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table;
for cur in cur_f loop
if cur.column_id <> v_mx_col_id then
/*
if cur.data_type='NVARCHAR2' then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
*/
if (cur.data_type ='VARCHAR2' or cur.data_type ='CHAR') then
if cur.char_length=4000 or (cur.char_length=120 and cur.data_length=480) then
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10),'||''''||'||chr(10)||'||''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10),'||' chr(32) '||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10)||chr(10),'||'chr(10)'||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
elsif cur.data_type ='NVARCHAR2' then
if cur.char_length=1000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
else
if cur.data_type='NUMBER' and cur.num_nulls =0 then
v_column_name := v_column_name||cur.column_name||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
end if;
else
/*
if cur.data_type='NVARCHAR2' then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
end if;
*/
if (cur.data_type ='VARCHAR2' or cur.data_type ='CHAR') then
if cur.char_length=4000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
elsif cur.data_type ='NVARCHAR2' then
if cur.char_length=1000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
end if;
end loop;
v_del :='delete from '||v_owner||'.'||v_table||';';
v_sql :='select '||v_column_name||' from '||v_owner||'.'||v_table;
-- dbms_output.put_line(v_sql);
fhandle := utl_file.fopen(v_owner||'_FILE_DIR',v_table||'.sql', 'w',32767);
utl_file.put_line(fhandle,'prompt '||v_owner||'.'||v_table||' Created on '||systimestamp);
utl_file.put_line(fhandle,'set feedback off');
utl_file.put_line(fhandle,'set define off');
utl_file.put_line(fhandle,v_del);
utl_file.put_line(fhandle,'commit;');
open cur_data for v_sql;
loop
fetch cur_data into v_show;
exit when cur_data%NOTFOUND;
utl_file.put_line(fhandle ,v_insrt);
-- utl_file.put_line(fhandle ,chr(10));
utl_file.put_line(fhandle ,v_show);
-- exit when cur_data%NOTFOUND;
end loop;
utl_file.put_line(fhandle ,'commit;');
utl_file.put_line(fhandle,'set feedback on');
utl_file.put_line(fhandle,'set define on');
utl_file.fclose(fhandle);
close cur_data;
/*exception
when others then
DBMS_OUTPUT.PUT_LINE ('Unexpected error');
RAISE;*/
end;
is
v_insrt varchar2(4000);
v_column_row varchar2(4000);
v_mx_col_id number;
v_owner varchar2(30);
v_table varchar2(30);
cursor cur_f is
select atc.COLUMN_NAME,atc.DATA_TYPE,atc.CHAR_LENGTH,atc.COLUMN_ID,atc.num_nulls,atc.data_length from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table order by atc.COLUMN_ID;
v_column_name varchar2(4000);
type curtype is ref cursor;
cur_data curtype;
fhandle utl_file.file_type;
v_sql varchar2(4000);
v_del varchar2(4000);
--v_mac varchar2(64);
v_show clob;
begin
v_owner := upper(trim(i_owner));
v_table := upper(trim(i_table));
v_column_name := '';
select wm_concat(atcs.COLUMN_NAME) into v_column_row from (select atc.column_name,atc.column_id from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table order by atc.column_id) atcs;
--select MACHINE into v_mac from v$session where username=v_owner;
v_insrt := 'insert into '||v_owner||'.'||v_table||'('||v_column_row||')'||' values(';
select max(atc.COLUMN_ID) into v_mx_col_id from all_tab_columns atc where atc.OWNER=v_owner and atc.TABLE_NAME=v_table;
for cur in cur_f loop
if cur.column_id <> v_mx_col_id then
/*
if cur.data_type='NVARCHAR2' then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
*/
if (cur.data_type ='VARCHAR2' or cur.data_type ='CHAR') then
if cur.char_length=4000 or (cur.char_length=120 and cur.data_length=480) then
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10),'||''''||'||chr(10)||'||''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
-- v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10),'||' chr(32) '||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
v_column_name := v_column_name||''''''''''||'||'||'replace('||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||',chr(10)||chr(10),'||'chr(10)'||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
elsif cur.data_type ='NVARCHAR2' then
if cur.char_length=1000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
else
if cur.data_type='NUMBER' and cur.num_nulls =0 then
v_column_name := v_column_name||cur.column_name||'||'||''''||','||''''||'||';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||','||''''||'||';
end if;
end if;
else
/*
if cur.data_type='NVARCHAR2' then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
end if;
*/
if (cur.data_type ='VARCHAR2' or cur.data_type ='CHAR') then
if cur.char_length=4000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
elsif cur.data_type ='NVARCHAR2' then
if cur.char_length=1000 then
v_column_name := v_column_name||''''''''''||'||'||'replace('||cur.column_name||','||''''''''''||','||''''''''''''''||')'||'||'||''''''''''||'||'||''''||');'||'''';
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
else
v_column_name := v_column_name||''''''''''||'||'||cur.column_name||'||'||''''''''''||'||'||''''||');'||'''';
end if;
end if;
end loop;
v_del :='delete from '||v_owner||'.'||v_table||';';
v_sql :='select '||v_column_name||' from '||v_owner||'.'||v_table;
-- dbms_output.put_line(v_sql);
fhandle := utl_file.fopen(v_owner||'_FILE_DIR',v_table||'.sql', 'w',32767);
utl_file.put_line(fhandle,'prompt '||v_owner||'.'||v_table||' Created on '||systimestamp);
utl_file.put_line(fhandle,'set feedback off');
utl_file.put_line(fhandle,'set define off');
utl_file.put_line(fhandle,v_del);
utl_file.put_line(fhandle,'commit;');
open cur_data for v_sql;
loop
fetch cur_data into v_show;
exit when cur_data%NOTFOUND;
utl_file.put_line(fhandle ,v_insrt);
-- utl_file.put_line(fhandle ,chr(10));
utl_file.put_line(fhandle ,v_show);
-- exit when cur_data%NOTFOUND;
end loop;
utl_file.put_line(fhandle ,'commit;');
utl_file.put_line(fhandle,'set feedback on');
utl_file.put_line(fhandle,'set define on');
utl_file.fclose(fhandle);
close cur_data;
/*exception
when others then
DBMS_OUTPUT.PUT_LINE ('Unexpected error');
RAISE;*/
end;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29802484/viewspace-1725230/,如需轉載,請註明出處,否則將追究法律責任。