首先对utl_file的执行授权:
grant execute on utl_file to penol;
然后在本地建立了一个存储过程:
create or replace procedure DATATOTXT as
tmpfile_handle UTL_FILE.file_type;
begin
tmpfile_handle:=UTL_FILE.fopen('DIR_DUMP','student.txt','w');
for x in(select * from TBLSTUDENT)
loop
UTL_FILE.put_line(tmpfile_handle,x.STUID||','||x.stuname||','||x.stuage||','||x.stu*||'');
end loop;
UTL_FILE.fclose(tmpfile_handle);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM,1,2000));
end;
tmpfile_handle UTL_FILE.file_type;
begin
tmpfile_handle:=UTL_FILE.fopen('DIR_DUMP','student.txt','w');
for x in(select * from TBLSTUDENT)
loop
UTL_FILE.put_line(tmpfile_handle,x.STUID||','||x.stuname||','||x.stuage||','||x.stu*||'');
end loop;
UTL_FILE.fclose(tmpfile_handle);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM,1,2000));
end;
目的:将TBLSTUDENT的数据按照指定的格式导入/home/oracle/student.txt
执行存储过程,数据成功导入,目前遇到的问题:utl_file将数据写入文本,中文出现乱码
,rhel5.4上的i18n设置为gbk,utf-8转gbk一定乱码,此问题有待解决!