Oracle建立外部表格

CREATE TABLE Extnl_Table

   (

        Col01 varchar2(100),

        Col02 Number,

        ......

   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "XXX"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY 0X'0A' SKIP 1 fields terminated by ',' enclosed by '"' LRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
    )
      LOCATION
       ( "CJ_DIR":'data.txt'
       )
    )

  ;

 

外部表格的定義關鍵是Oracle Loader參數，Records關鍵字後定義如果識別資料行，Fields關鍵字後定義如果識別欄位，常用的如下：

 Records:

       DELIMITED BY 'XXX' —— 分行符號，常用newline定義換行，如果檔案中使用了特別的字元就要另外定義了；如果是特殊符號，可以使用OX'十六位值'，例如tab(\t)的十六位是9，那麼就是DELIMITED BY 0X'09'；cr(\r)的十六位是d，那麼就是DELIMITED BY 0X'0D'。

       SKIP X —— 跳過X行資料，有些檔案中第一行是列名，需要跳過第一行，則使用SKIP 1。

Fields：

       TERMINATED BY 'x' —— 欄位分割符。

       ENCLOSED BY 'x' —— 欄位引用符，包含在此符號內的資料都當成一個欄位。例如一行資料格式如："abc","a""b,""c,"。使用參數TERMINATED BY ',' ENCLOSED BY '"'後，系統會讀到兩個欄位，第一個欄位的值是abc，第二個欄位值是a"b,"c,。

       LRTRIM —— 刪除空白字元。

       MISSING FIELD VALUES ARE NULL —— 空缺值都設為Null。

 

如果外部表格檔案的欄位長度和分割符不確定，可以開啟檔案看看。但是如果檔案非常大，比如用十幾G，用UltraEdit之類的工具開啟也許會等十幾個小時。那麼可以使用分割檔案的工具將檔案切割成1M左右的小檔案，再用記事本開啟，複製幾行資料出來作測試用。

 

讀取外部表格時最常見的問題就是報Buffer不足了，如果增大Read Size還不能解決問題，可能就是斷行錯誤，也許是分行符號不對。因為有些分行符號在記事本中看不出來，比如說Windows的分行符號是\n\r，而Mac等系統中是Line Feed(LF, 0x0A)。如果在Windows中開啟帶LF的檔案，記事本中顯示的是一個黑色的方塊，而寫字板中是能夠顯示正確的換行。所以最好使用UltraEdit開啟，直接看16進位代碼，使用DELIMITED BY 0X'xx'斷行。

 

還有可能在從外部表格讀取資料時，讀取前面的資料沒問題，讀到一定的行數就報錯。這可能是某行資料出問題導致。只是可以在外部檔案目錄下發現檔案名稱帶有"BAD"的記錄檔，其中就儲存了出錯的資料，有記事本開啟看看那裡出錯，是否與外部表格定義衝突。