procedure load_PsTableToFile(tablename IN VARCHAR2) Is
w_rec user_tab_cols%rowtype;
v_sql varchar2(32000) :=
'declare
l_file_handle UTL_FILE.file_type;
v_colname varchar2(50);
v_data_type varchar2(20);
i_data_length integer;
v_data_precision integer;
i_data_scale integer;
type C_COL is ref cursor;
w_col C_COL;
type c_CurRef is ref cursor;
r_curref c_CurRef;
w_rec '||tablename||'@rpt2ps%rowtype;
v_temp varchar2(2000);
v_line varchar2(5000);
begin
open r_curref for ''select * from '||tablename||'@rpt2ps'';
fetch r_curref into w_rec;
open w_col for ''select column_name, data_type, data_length, data_precision, data_scale
from user_tab_cols@rpt2ps t
where table_name = upper('''''||tablename||''''')
order by column_id'';
fetch w_col into v_colname,v_data_type,i_data_length,v_data_precision,i_data_scale;
v_line:=v_line||rpad(nvl(w_rec.v_colname,'' ''),i_data_length,'' '');//这里如何实现把第一列的内容取出来?
dbms_output.put_line(v_line);
end;';
begin
execute immediate v_sql;
exception
when others then
v_errcode:=sqlcode;
v_errmsg:=substr(sqlerrm,1,300);
insert into cib_errer_table(errcode,errmesg) values(v_errcode,v_errmsg);
commit;
end load_PsTableToFile;
我这里有100多张表需要把里面的数据导成文件。但是这里管理非常严格,只提供了连接到oracle数据库的客户端工具,所以不能用spool等等工具进行数据库数据的导出,只能用存储过程进行数据的导出。由于表太多,一张张表来导出的话,数据量是非常大的,我现在想写一个公共函数,只要用户输入表名就导出相应的表。上面是我写的存储过程。但是到v_line:=v_line||rpad(nvl(w_rec.v_colname,'' ''),i_data_length,'' '');就报错了。不知道各位高手是否有更好的方法,不吝指教。
http://blog.csdn.net/java3344520/archive/2010/05/28/5631277.aspx
declare
vtable varchar2(36);
vcolumn varchar2(36);
vsql varchar2(4000);
type refcursor is ref cursor;
cur_data refcursor;
vrecord varchar2(4000);
cursor cur_table_desc(ptable varchar2) is
select column_name from user_tab_columns where table_name = ptable;
begin
vtable := 'REGIONS';
vsql := 'select ';
open cur_table_desc(vtable);
loop
fetch cur_table_desc into vcolumn;
exit when cur_table_desc%notfound;
vsql := vsql || vcolumn || '||'',''||';
end loop;
close cur_table_desc;
vsql := substr(vsql, 1, length(vsql) - 7);
vsql := vsql || ' from ' || vtable;
dbms_output.put_line(vsql);
open cur_data for vsql;
loop
fetch cur_data into vrecord;
exit when cur_data%notfound;
dbms_output.put_line(vrecord);
end loop;
close cur_data;
end;-- 结果
select REGION_ID||','||REGION_NAME from REGIONS
1,Europe
2,Americas
3,Asia
4,Middle East and Africa