使用oracle的utl_file包将数据导入到文本

1.设参数。alter system set utl_file_dir='/opt/oracle/product/' scope=spfile;
2.重启让参数生效。startup force;
3.写存储过程。例如将表emp导入到output.txt中。
1  declare
  2  v_filehandle UTL_FILE.FILE_TYPE;
  3  begin
  4  v_filehandle:=utl_file.fopen('/opt/oracle/product/','output.txt','w');
  5  UTL_FILE.PUTF (v_filehandle,'time is :%s/n', SYSDATE);
  6  UTL_FILE.NEW_LINE (v_filehandle);
  7  for i in(select * from dept) loop
  8  UTL_FILE.PUTF (v_filehandle, '%s ,%s, %s/n',i.deptno,i.dname,i.loc);
  9  end loop;
10  UTL_FILE.FCLOSE (v_filehandle);
11 end;
4.查看。
[oracle@localhost product]$ ls
10.2.1  output.txt
[oracle@localhost product]$ cat output.txt
time is :22-OCT-10

10 ,ACCOUNTING, NEW YORK
20 ,RESEARCH, DALLAS
30 ,SALES, CHICAGO
40 ,OPERATIONS, BOSTON