将oracle导出成文本文件,oracle 数据能否导出成纯文本文件呢？

--需要打开初始化参数utl_file_dir,重新db生效.(utl_file_dir = *).

--alter system set utl_file_dir=* scope=spfile;

--shutdown immediate

--startup

create or replace procedure output_table_data

(

i_tablename in varchar2,

o_result out number,

o_errormessage out varchar2

)

as

cursor c1(m_tablename varchar2) is select column_name from user_tab_columns

where table_name=m_tablename order by column_id;

C2                  C1%rowtype;

m_result          number;

m_errormessage varchar2(512);

m_columns          varchar2(10000);

m_sql          varchar2(10000);

m_otext          varchar2(10000);

beginflag          number;

the_c1          integer;

fdbk                  INTEGER;

f_handle          utl_file.file_type;

begin

beginflag :=0;

open c1(i_tablename);

loop

fetch c1 into c2;

exit when c1%notfound or c1%notfound is null;

--dbms_output.put_line('m_columns='||m_columns);

if beginflag = 0 then

m_columns := '''"'''||'||replace("'||c2.column_name||'"'||','||'''"'''

||','||''''''||')'||'||'||'''"''';

else

m_columns := m_columns||'||'||''','''||'||'||'''"'''||'||replace("'||

c2.column_name||'"'||','||'''"'''||','||''''''||')'||'||'||'''"''';

end if;

beginflag := 1;

end loop;

close c1;

m_sql :='select '||m_columns||' from '||i_tablename;

the_c1 :=dbms_sql.open_cursor;

dbms_sql.parse(the_c1,m_sql,dbms_sql.v7);

dbms_sql.define_column(the_c1,1,m_otext,3800);

fdbk := dbms_sql.execute(the_c1);

dbms_output.put_line('fdbk='||fdbk);

f_handle:=utl_file.fopen('c:\',i_tablename||'.txt','w',32767);

loop

--fetch next row. exit when done.

exit when dbms_sql.fetch_rows (the_c1) = 0;

dbms_sql.column_value (the_c1, 1,m_otext);

utl_file.put_line(f_handle,replace(replace(m_otext,chr(10),'') ,chr(13),''));

end loop;

dbms_sql.close_cursor(the_c1);

utl_file.fclose(f_handle);

o_result:=0;

exception

when others then

dbms_sql.close_cursor(the_c1);

utl_file.fclose(f_handle);

m_result := sqlcode;

m_errormessage := m_result||substr(sqlerrm,1,200);

o_result:=-1;

o_errormessage := m_errormessage;

end;

/

--示例:文件生成在根目录c:\  下面,如果是unix,自己改一下存储过程中路径(c:\)

var m_result number;

var m_errormessage varchar2(512);

exec output_table_data('TABLE_NAME',:m_result,:m_errormessage);

print m_result;

print m_errormessage;

--生成的文件如下:c:\test.txt,用"括上字符串,这样可以防止一些长字符串中有非法的字符,如逗号之类的,用","分隔.

"1","test"

"2","test2"