使用utl_file做選擇性資料導出

在平時的資料導出中使用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中。

UTL_FILE.PUTF (v_filehandle,'---export data from table data:', SYSTIMESTAMP);

for i in(select * from data where rownum

UTL_FILE.PUTF (v_filehandle, '%s,%s\n',i.zhu,i.ke);

end loop;

輸出内容如下，可以看到都是按照逗号分隔。顯示的情況還不錯。

---export data from table data:

0,2

2,1

1,2

1,0

3,1

0,1

0,3

2,2

6,2

0,0

3,0

因為utl_file在新版本中一直都是推薦使用directory來替代的，我們也可以使用directory對象來實作。黃色的部分TEST就是directory的名字，指向'/u01/ora11g/test/test'

v_filehandle:=utl_file.fopen('TEST','output.txt','w');

輸出的結果沒有任何變化。

有的人可能說是用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

  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

1,突破玩法界限