公众号：杨建荣的学习笔记 · 作者：r2笔记95天 · 发布：2014-09-14 23:08:30 · 原文链接

在平时的数据导出中使用exp/expdp能够满足绝大部分的数据导出任务。如果有一些表的数据不多，但是查询条件要复杂一些，使用exp/expdp就很吃力了。
或者在和外部系统的交互中，使用xml或者文本文件是一个很兼容的选择，这个时候使用exp/expdp也满足不了要求。
这个时候可以考虑使用utl_file的提供的一些功能来做选择性的数据导出。
先来使用utl_file做一个简单的例子，输出两行文本内容到output.txt文件中。一行Hello,一行hello word
declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen(‘/u01/ora11g/test/test’,’output.txt’,’w’);
UTL_FILE.PUTF (v_filehandle,’ REPORT: GENERATED ON%s\n’, SYSDATE);
UTL_FILE.NEW_LINE (v_filehandle);
UTL_FILE.PUTF (v_filehandle, ‘%s\n’,’hello ‘);
UTL_FILE.PUTF (v_filehandle, ‘hello: %s\n’,’world ‘);
UTL_FILE.FCLOSE (v_filehandle);
end;
/

运行pl/sql之后的输出如下：

[ora11g@rac1 test]$ cat output.txt
REPORT: GENERATED ON14-SEP-14

hello
hello: world

这个地方需要说明一下，我在 /u01/ora11g/test/test 输出了文件output.txt,事先没有创建任何的directory。因为utl_file_dir这个参数的默认值是*
SQL> show parameter utl

NAME TYPE VALUE
———————————— ———– ——————————
create_stored_outlines string
utl_file_dir string *

我们来做一个更有实际意义的。
从表data中输出100行数据到output.txt中。
declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen(‘/u01/ora11g/test/test’,’output.txt’,’w’);
UTL_FILE.PUTF (v_filehandle,’—export data from table data:’, SYSTIMESTAMP);
UTL_FILE.NEW_LINE (v_filehandle);
for i in(select * from data where rownum<100) loop
UTL_FILE.PUTF (v_filehandle, ‘%s,%s\n’,i.zhu,i.ke);
end loop;
UTL_FILE.FCLOSE (v_filehandle);
end;
/

输出内容如下，可以看到都是按照逗号分隔。显示的情况还不错。
[ora11g@rac1 test]$ cat output.txt
—export data from table data:
0,2
2,1
1,2
1,0
3,1
0,1
0,3
2,2
6,2
1,0
0,2
0,0
3,0
1,0
1,2
0,1

因为utl_file在新版本中一直都是推荐使用directory来替代的，我们也可以使用directory对象来实现。黄色的部分TEST就是directory的名字，指向’/u01/ora11g/test/test’

declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen(‘TEST’,’output.txt’,’w’);
UTL_FILE.PUTF (v_filehandle,’—export data from table data:’, SYSTIMESTAMP);
UTL_FILE.NEW_LINE (v_filehandle);
for i in(select * from data where rownum<100) loop
UTL_FILE.PUTF (v_filehandle, ‘%s,%s\n’,i.zhu,i.ke);
end loop;
UTL_FILE.FCLOSE (v_filehandle);
end;
/

输出的结果没有任何变化。

有的人可能说是用spool也可以实现，而且更灵活，在一定程度上是的，不过还是和utl_file有一定的区别。
比如我没有设置NLS_LANG的变量值，在sqlplus中查看中文可能就有问题。但是系统层面没有任何影响。

可以看到在sqlplus中显示是乱码的形式，但是在输出文件中显示的是正确的中文格式。
SQL> select *from test;

ID NAME
———- —————————–
1 ??????

SQL> declare
2 v_filehandle UTL_FILE.FILE_TYPE;
3 begin
4 v_filehandle:=utl_file.fopen(‘/u01/ora11g/test/test’,’output.txt’,’w’);
5 UTL_FILE.PUTF (v_filehandle,’—export data from table data:’, SYSTIMESTAMP);
6 UTL_FILE.NEW_LINE (v_filehandle);
7 for i in(select * from test where rownum<100) loop
8 UTL_FILE.PUTF (v_filehandle, ‘%s,%s\n’,i.id,i.name);
9 end loop;
10 UTL_FILE.FCLOSE (v_filehandle);
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> host
[ora11g@rac1 test]$ cat output.txt
—export data from table data:
1,突破玩法界限