ORACLE_LOADERドライバでの外部表

ORACLE_LOADERドライバでの外部表の作成

データファイルの仕様

[load_dir oracle(PROD)] head book.dat
150     2009/06/02 22:51:03     BK0000  CONCEPTS        1
151     2009/06/01 22:51:03     BK0001  ADMIN   2
152     2009/05/31 22:51:03     BK0002  RMAN    3
153     2009/05/30 22:51:03     BK0003  SQL     4
154     2009/05/29 22:51:03     BK0004  TUNING  5
155     2009/05/28 22:51:03     BK0005  RAC     1
156     2009/05/27 22:51:03     BK0006  DG      2
157     2009/05/26 22:51:03     BK0007  DWH     3
158     2009/05/25 22:51:03     BK0008  NETWK   4
159     2009/05/24 22:51:03     BK0009  PLSQL   5
... ...

WINDOWSとLINUXの改行コードが違うので、データファイルはWINDOWSで作成された場合、下記のコマンドでLINUXで改行を変換する。
tr -d \\r <win_book.dat> book.dat

外部表仕様
 表名: TOM.EXT_BOOK
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                           NUMBER
 ORDER_DATE                                         DATE
 BOOK_NO                                   NOT NULL VARCHAR2(20)
 BOOK_TYPE                                 NOT NULL VARCHAR2(20)
 BOOK_CNT                                  NOT NULL NUMBER

外部データを格納するディレクトリ・オブジェクトを作成
PROD(SYS)>CREATE OR REPLACE DIRECTORY LOAD_DIR AS '/u01/app/oracle/load_dir';

ディレクトリが作成されました。

PROD(SYS)>grant read,write on directory load_dir to tom;

権限付与が成功しました。

PROD(SYS)>

外部表の作成
CREATE TABLE TOM.EXT_BOOK (
        ORDER_ID                NUMBER,
        ORDER_DATE              DATE,
        BOOK_NO                 VARCHAR2(20),
        BOOK_TYPE               VARCHAR2(20),
        BOOK_CNT                NUMBER )
        ORGANIZATION EXTERNAL (
                TYPE ORACLE_LOADER
                DEFAULT DIRECTORY LOAD_DIR
                ACCESS PARAMETERS (
                        FIELDS TERMINATED BY 0X'09'
                                (       ORDER_ID,
                                        ORDER_DATE CHAR DATE_FORMAT DATE MASK 'yyyy/mm/dd hh24:mi:ss',
                                        BOOK_NO,
                                        BOOK_TYPE,
                                        BOOK_CNT
                                )
                )
                LOCATION('book.dat')
        )
        REJECT LIMIT UNLIMITED;

外部表データの確認
PROD(TOM)>select * from ext_book where rownum < 11;

  ORDER_ID ORDER_DA BOOK_NO              BOOK_TYPE              BOOK_CNT
---------- -------- -------------------- -------------------- ----------
       150 09-06-02 BK0000               CONCEPTS                      1
       151 09-06-01 BK0001               ADMIN                         2
       152 09-05-31 BK0002               RMAN                          3
       153 09-05-30 BK0003               SQL                           4
       154 09-05-29 BK0004               TUNING                        5
       155 09-05-28 BK0005               RAC                           1
       156 09-05-27 BK0006               DG                            2
       157 09-05-26 BK0007               DWH                           3
       158 09-05-25 BK0008               NETWK                         4
       159 09-05-24 BK0009               PLSQL                         5

10行が選択されました。

PROD(TOM)>select count(*) from ext_book;

  COUNT(*)
----------
    100000

PROD(TOM)>

ACCESS PARAMETERS句の主なパラメータとオプション

RECORD FORMAT定義   RECORDS …

FIXED
FIXED句を使用して、すべてのレコードをバイト単位の固定長として識別する。
VARIABLE
VARIABLE句を使用して、レコードを可変長として識別する。
DELIMITED BY {NEWLINE | string}

  • レコードの終わりを識別する文字を指定する。
  • DELIMITED BY NEWLINEを指定する場合、実際に使用される値はプラットフォームに依存する。UNIXプラットフォームでは、終了文字は「\n」である。Windowsでは、終了文字は「\r\n」である。
  • stringは、テキストまたは一連の16進数のいずれかになる。16進数の場合は、OXまたはXで開始して引用符で囲む。

CHARACTERSET
CHARACTERSET string句を使用して、データ・ファイルのキャラクタ・セットを識別する。
STRING SIZES ARE IN

  • STRING SIZES ARE IN句を使用して、文字列の長さがバイト単位であるか、または文字単位であるかを指定する。
  • UTF16などのマルチバイト・キャラクタ・セットのロード時には、STRING SIZES ARE IN CHARACTERSを指定する必要がある。

LOAD WHEN
データベースに渡すレコードを識別する。

--# LOAD WHEN 使用例
PROD(TOM)>CREATE TABLE TOM.EXT_BOOK (
        ORDER_ID                NUMBER,
        ORDER_DATE              DATE,
        BOOK_NO                 VARCHAR2(20),
        BOOK_TYPE               VARCHAR2(20),
        BOOK_CNT                NUMBER )
        ORGANIZATION EXTERNAL (
                TYPE ORACLE_LOADER
                DEFAULT DIRECTORY LOAD_DIR
                ACCESS PARAMETERS (
                        RECORDS DELIMITED BY NEWLINE LOAD WHEN (BOOK_TYPE="RAC") -- RACのみを抽出
                        FIELDS TERMINATED BY 0X'09'
                                (       ORDER_ID,
                                        ORDER_DATE CHAR DATE_FORMAT DATE MASK 'yyyy/mm/dd hh24:mi:ss',
                                        BOOK_NO,
                                        BOOK_TYPE,
                                        BOOK_CNT
                                )
                )
                LOCATION('book.dat')
        )
        REJECT LIMIT UNLIMITED;
 
表が作成されました。
 
PROD(TOM)>select count(*) from ext_book;
 
  COUNT(*)
----------
     10000
 
PROD(TOM)>select distinct book_type from ext_book;
 
BOOK_TYPE
--------------------
RAC
 
PROD(TOM)>

BADFILE | NOBADFILE
  • エラーのためにロードできない場合にレコードが書き込まれるファイルを指定する。
  • NOBADFILEオプションを使用して不良ファイルの作成を回避できる。
  • BADFILEまたはNOBADFILEのいずれも指定しない場合、デフォルトでは1つ以上のレコードが拒否されると、不良ファイルが作成される。

LOG FILE | NOLOGFILE

  • LOGFILE句を使用してデータ・ファイルのデータへのアクセス中に外部表のユーティリティによって生成されたメッセージを含むファイルを指定する。
  • NOLOGFILEを使用してログ・ファイルの作成を回避できる。
  • LOGFILEまたはNOLOGFILEのいずれも指定しない場合、デフォルトではログ・ファイルが作成される。

READSIZE

  • READSIZEパラメータは、読取りバッファのサイズを指定する。
  • デフォルト値は512KB(524288バイト)である。データ・ファイル内に512KBより大きいレコードがある場合は、デフォルト値より大きい値を指定する必要がある。
FIELD定義句

ENCLOSED BY | TERMINATED BY string | OPTIONALLY
終了デリミタを含む外部表

CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY WHITESPACE)
                         LOCATION ('info.dat'));

Alvin Tolliver 1976
Kenneth Baer 1963
Mary Dube 1973

囲みデリミタおよび終了デリミタを含む外部表
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4)) 
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                        ACCESS PARAMETERS (FIELDS TERMINATED BY "," ENCLOSED BY "("  AND ")")
                        LOCATION ('info.dat'));

(Alvin) ,   (Tolliver),(1976)
(Kenneth),  (Baer) ,(1963)
(Mary),(Dube) ,   (1973)

オプションの囲みデリミタを含む外部表
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY ','
                                            OPTIONALLY ENCLOSED BY '(' and ')'
                                            LRTRIM)
                         LOCATION ('info.dat'));

Alvin ,   Tolliver , 1976
(Kenneth),  (Baer), (1963)
( Mary ), Dube ,    (1973)

NOTRIM | LRTRIM | LTRIM | RTRIM | LDRTRIM
  • LDRTRIMは、デフォルトある。
    • フィールドがデリミタ付きのフィールドではない場合、空白は右から切り捨てられる。
    • フィールドがOPTIONALLY ENCLOSED BYで指定されたデリミタ付きフィールドで、オプションの囲みが特定のインスタンスで欠落している場合、空白は左から切り捨てられる。
  • NOTRIMを指定すると、パフォーマンスが向上する。

MISSING FIELD VALUES ARE NULL

  • レコードのすべてのフィールドに十分なデータがない場合、データ値が欠落しているフィールドがNULLに設定されることを示す。
  • MISSING FIELD VALUES ARE NULLを指定せず、レコードのすべてのフィールドに十分なデータがない場合、行は拒否される。

詳細情報

下記のORACLEリファレンスを参照してください。
ORACLE社のリファレンス

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License