淺說兩種輸出Oracle字元檔案的方法

淺說兩種輸出Oracle字元檔案的方法

歸納幾條簡單方便的經驗之談吧。前幾天，一個同事為做項目和筆者討論將資料庫檢索處理結果輸出的方法。為了簡單明了，筆者按照不同的需求情境準備了兩種策略供同事進行選擇，記錄下來，權作不時之需的留存。

文字檔輸出，特別是大資料量文字檔輸出，是我們在實際需求領域中經常遇到的情境。文字檔是我們最早接觸的檔案格式，格式單一，內容簡單。但是，也正是因為結構簡單，是很多“中間結構檔案”通常選擇的載體。從最早簡單的txt、csv，到現在越來越多出現xml，本質上都是以文字格式設定檔案進行儲存。

所謂“中間結構檔案”，也就是我們通常所說的介面檔案。如果系統之間需要進行大規模資料轉送、接入或者互動，雙方共同認可的“協議”也就是問題的關鍵。通過介面檔案格式外加自動化上傳、定位和檢索機制，是可以實現解耦方式的系統間資料互動。

Oracle環境中通常使用的文本產生方式傳統上有兩種，一種是藉助原生的sqlplus命令列工具，將資料轉出到用戶端目錄上。注意：sqlplus命令列系列預設輸出是螢幕。另一種是藉助utl_file工具包將資料輸出到資料庫服務端（Server Side）。兩種方法各有利弊優缺點，各有適應的情境。下面分別進行討論。

1、實驗環境介紹

筆者使用Oracle 11gR2版本進行測試，具體版本為11.2.0.4。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2、Sqlplus的Spool方法

Spool方法是指令碼工程師比較常用的策略。思路其實也比較簡單：在傳統的編程結構中，資料處理結果的輸出是有一個導向定位機制的。預設情況下，Sqlplus系工具的輸出是螢幕視窗。Spool方法就是重新設定輸出方式，將結果常值內容輸出到磁碟檔案中。

使用spool檔案有兩個方面需要關註：首先是檔案位置。產生的檔案，無論是Linux/AIX檔案還是Windows系列，都是在用戶端所在的電腦（執行程式的機器）上產生。另一個就是文本量限制，無論是使用sqlplus、還是PL/SQL Developer的Command Windows視窗，都會遇到潛在的緩衝區buffer溢出風險。這也就限制了組建檔案的大小。

下面我們通過一個簡單實驗來進行證明，實驗資料表結構如下：

SQL> desc test_user;

Name            Type        Nullable Default Comments                                                                                                         

---------------- ------------ -------- ------- ---------------------------------------------------------------------------------------------------------------- 

OBJECT_ID        CHAR(32)                      String - Object Id                                                                                               

USER_ID          CHAR(32)    Y                String - Unique User ID                                                                                         

FIRSTNAME        CHAR(50)    Y                String - Users first name                                                                                       

LASTNAME        CHAR(50)    Y                String - Users last name                                                                                         

SHORTNAME        CHAR(5)      Y                String - Users short name                                                                                       

IS_ACTIVE        CHAR(1)      Y                Character - Boolean                                                                                             

在sqlplus命令列中依次執行：

SQL> spool d:\spool_test.txt

Started spooling to d:\spool_test.txt

SQL> 

SQL> set echo off;

SQL> set feedback off;

SQL> set newpage none;

SQL> set linesize 1000;

SQL> set pagesize 0;

SQL> set term off;

SQL> set timing off;

SQL> set verify off;

SQL> select trim(USER_ID)||','||trim(FIRSTNAME)||','||trim(LASTNAME)||','||trim(IS_ACTIVE)

  2  from test_user;

SQL> spool off;

Stopped spooling to d:\spool_test.txt

注意幾個細節問題：

首先在SQL>命令提示字元，使用spool命令，就可以啟動/關閉檔案寫入磁碟動作。Spool on就是預設開啟命令，而spool後加入路徑就指定了檔案目錄名稱。關閉寫入spool off後，檔案寫入動作自動停止。注意：從檔案大小角度看，只有在spool off的時候，通常系統才將結果從緩衝區寫入到檔案中。

其次是sqlplus參數配置。Sqlplus易用難精，有很多控制參數用於輸出輸入資料方式。對於一般使用者而言，可以直接保留一份固定的控制參數模板，對相同需求的命令可以直接使用。

組建檔案之後，我們就可以在用戶端機器的目錄上找到對應檔案。其中內容恰好是滿足逗號分隔資料要求的。

111,222,222,Y

111,2222,33,Y

11,222,33,Y

（篇幅原因，有省略…..）

Spool方法的優點是很明顯的，就是簡單易用，對使用使用者權限要求低，只要能夠使用sqlplus工具，就可以組建檔案。同時，組建檔案在用戶端，也不需要DBA和系統管理員設定目錄許可權管理空間消耗。

同時，spool方法的缺點也是比較明顯的，就是組建檔案大小限制。進入11g之後，sqlplus在緩衝區上有所擴大，但是依然還是很大的工作隱患。站在系統自動化的角度，產生的介面檔案存放在用戶端也不是一種規範的做法，不利於後續自動化傳輸處理。

總而言之，spool方法比較適用於小規模、簡單資料檔案的產生。

3、utl_file包使用

UTL_FILE是Oracle官方推薦的一種經典檔案產生方法，主要原則是通過utl_file包來進行文字檔讀寫動作。與spool方法最大的區別，在於utl_file包主要是產生在資料庫伺服器端（也就是Oracle Instance啟動並執行伺服器上），同時utl_file包對於檔案讀寫的控制更加細粒度化，以一種類似於C語言的方式進行檔案讀寫。

從目前看，utl_file組建檔案依然是從資料庫端組建檔案比較成熟的方案，特別是大介面檔案。在一些高效能需求的情境下，還是有競爭力的。

對於utl_file包，不能不說到參數utl_file_dir。在Oracle 9.2之前，這個參數是產生讀寫utl_file執行的最重要參數。

SQL> show parameter utl_file

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                        string     

在現在我們在網路查詢資料時，還是能夠看到對utl_file_dir參數的設定要求。在9.2之前的版本中，如果進行檔案讀寫，都需要這個這個參數，將讀寫檔案的所在目錄添加到其上。否則Oracle就不能承認這個目錄下的檔案操作許可權。略麻煩的是，這個參數修改要在spfile中進行，生效就需要重啟伺服器。

這種情境在9.2版本之後有了變化，directory對象的出現，提供了更好的讀寫目錄管理和許可權管理。最大的一個好處，就是在代碼中，可以不用寫入程式碼方式寫目錄結構。所以，當前utl_file_dir目錄基本不會再使用，只是出於系統相容性目的。

使用utl_file包方法使用如下步驟：

步驟1：建立directory目錄

[oracle@sicslife /]$ su - root

Password: 

--確保作業系統層面的許可權！

[root@sicslife ~]# cd /

[root@sicslife /]# chown -R oracle:oinstall /upload/

[root@sicslife /]# ls -l | grep upload

drwxr-xr-x.  4 oracle oinstall  4096 Aug  6 21:21 upload

SQL> create directory utl_path as '/upload';

SQL>

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME                DIRECTORY_PATH

------------------------------ ------------------------------------------------

UTL_PATH                      /upload

使用directory要解決兩個層面許可權，一個是作業系統層面，要讓Oracle作業系統使用者可以使用目錄。另一個是directory對象使用權，要進行顯示的授權。

SQL> grant write on directory utl_path to scott;

Grant succeeded

SQL> grant execute on utl_file to scott;

Grant succeeded

最後，可以在代碼中進行調用。

SQL> set serveroutput on size 1000;

SQL> declare

  2    out_file utl_file.file_type; --檔案類型，也就是控制代碼對象

  3    vc_file_name varchar2(100);

  4    vc_line varchar2(100);

  5    i number;

  6  begin

  7    vc_file_name := 'utl_file_test.txt';

  8 

  9    out_file := utl_file.fopen('UTL_PATH',vc_file_name,'w'); --寫方式開啟檔案

 10 

 11    if (utl_file.is_open(out_file)) then

 12      for i in 1..100 loop

 13          vc_line := to_char(i)||','||i||'Lines~';

 14          utl_file.put_line(out_file,vc_line);

 15      end loop;

 16    else

 17      dbms_output.put_line('Open Failure~');

 18    end if;

 19 

 20    utl_file.fclose(out_file); --和C語言一樣，需要顯示進行關閉

 21  end;

 22  /

PL/SQL procedure successfully completed

最後，就可以在作業系統層面，找到對應檔案。

[root@sicslife /]# cd /upload/

[root@sicslife upload]# ls -l

total 12

drwxr-xr-x. 7 oracle oinstall 4096 Aug 27  2013 database

drwx------. 3 oracle oinstall 4096 Aug  5 17:26 igb-5.3.2

-rw-r--r--. 1 oracle oinstall 1184 Aug  7 02:19 utl_file_test.txt

[root@sicslife upload]# cat utl_file_test.txt 

1,1Lines~

2,2Lines~

3,3Lines~

4,4Lines~

5,5Lines~

6,6Lines~

（篇幅原因，有省略…….）

 [root@sicslife upload]#

Utl_file包是一種比較成熟的檔案讀寫方案，除了執行個體中操作的步驟方法之外，還定義了很多有用的讀寫方法、異常類型，這對於我們進行完善編程是很有意義的。同時，在實際應用中，utl_file有著更多的細節因素和限制特性，本篇不予累述。

3、結論

利用資料庫組建檔案，是非常常見的需求。在不藉助第三方工具的情況下，spool和utl_file是不錯的工具選擇。