Oracle/SqlLoader

Oracle SQL*Loader

SQL*Loader は、データファイル(CSV,TSVや固定長など)からOracleのテーブルにデータを流し込むツール。ロードするデータファイルとその構造やテーブルとの対応付けを定義した制御ファイルを用意する。

起動オブション

CONTROL=sample.ctl: 使用する制御ファイルを指定する。

LOG=sample.log: 実行時のログを出力するログファイルを指定する。

DIRECT=Y: ダイレクト・パスでデータをロードするとき指定する。従来型パスに比べ驚異的なパフォーマンス(10倍近く)が出る。ただし、制約も多いので用途・目的応じて使い分けること。

制御ファイル

制御ファイルの文字コードは環境(NLS_LANGで指定された文字コード)に合わせる。

また、各行で、"--" 以降はコメントとして扱われる。

OPTIONS

SQL*Loader に渡す引数を記述できる。

LOAD: ロードする論理レコードの最大件数を指定する。デフォルトはすべて

SKIP: 先頭から読み飛ばす論理レコード件数を指定する。デフォルトはなし(0件)

ERRORS: 許容するエラーの数を指定する。指定した値を超えると中止する。(-1の場合は、無制限？)

ROWS: 何件毎にコミットするかを指定する。(-1の場合は、全件コミット？)

LOAD DATA

お約束。以降に読み込みために必要な定義(制御情報)を記述する。

CHARACTERSET

データファイルの文字コードを指定する。

INFILE

データファイル名を指定する。相対パスは実行時のカレントディレクトリからとなる。複数のデータファイルがある場合は、必要分をかき並べる。

また、「INFILE *」とすると対象のデータが制御ファイルの終わりにあることを示します。制御情報のあとに「BEGINDATA」を記述してデータを記述します。

BADFILE

不良ファイル名を指定する。何らかの理由でDBへロードすることが出来なかった論理レコードが出力される。指定がない場合は、必要に応じてデフォルトのファイル名(データファイル.bad)で作成される。

DISCARDFILE

廃棄ファイル名を指定する。WHEN条件によって弾かれた論理レコードが出力される。指定がない場合は、廃棄ファイルは作成されない。

INSERT, APPEND, REPLACE, TRUNCATE

データをロードするモード、以下のいずれかを指定する。

INSERT: 空のデータを表に追加する。テーブルが空でない場合はエラーとなる。

APPEND: 表にデータを追加する。既にデータがある場合、重複しないレコードのみが追加される。

REPLACE: 表のデータをすべて入れ替える。既存のデータは、DELETE してからロードされる。

TRUNCATE: 表のデータを切り捨てて追加する。既存のデータは、TRUNCATE してからロードされる。TRUNCATE 出来る権限が必要となる。

INTO TABLE table_name

データを入れる表名を指定する。

WHEN condition

データを入れる条件を指定する。書き方は、SQLのWHERE句と同様。条件に一致しないものは、廃棄ファイルに出力される。

FIELDS TERMINATED BY "～" OPTIONALLY ENCLOSED BY '～'

FIELDS
 [TERMINATED BY (WHITESPACE|X'xx'|'str'|EOF)
 [OPTIONALLY] [ENCLOSED BY (X'xx'|'str') [AND (X'xx'|'str')]

デフォルトの項目の区切り方を指定する。可変長データの区切り文字と必要なら囲み文字を指定する。固定長の場合は、項目ごとに POSITION で指定する。

カンマ区切りの場合は、「TERMINATED BY ","」
タブ区切りの場合は、「TERMINATED BY X'09'」
ダブルクォーテーションで囲まれている場合は、「OPTIONALLY ENCLOSED BY '"'」

TRAILING NULLCOLS

データがない場合に、NULLを入れるように指定する。可変長で項目数が足りない場合に指定があればNULLとなるが、ない場合はエラーとなる。

PRESERVE BLANKS

通常 SQL*Loader は、空白文字を切り捨てます。これを行わないようにするときに指定します。ただし、すべての列に対して適応されるので、TRIM関数などで不要な空白を取り除くようにします。

制御ファイル記述例

----------------------------------------------------------------------
--  Oracle SQL*Loader Control File
--
--     FileName  sample.ctl
--     MakeDate  2006/01/01 12:34:56
--     Creator   SqlSaver Ver.1.xx
----------------------------------------------------------------------
-- === execution command and option ===
-- sqlldr USER/PASS@TSN CONTROL=sample.ctl LOG=sample.log ...
----------------------------------------------------------------------
OPTIONS(ROWS=50000, ERRORS=100000000)

LOAD DATA
-- === file name specification ===
INFILE      'sample.dat' -- Data file
BADFILE     'sample.bad' -- Bad file
DISCARDFILE 'sample.dis' -- Discard file

-- === data insert table specification ===
INTO TABLE sample

-- === data insert method specification ===
--  mode is INSERT, APPEND, REPLACE, TRUNCATE
TRUNCATE

TRAILING NULLCOLS (
-- === layout definition specification ===
　　：
)

----------------------------------------------------------------------
--  End Of File (sample.ctl)
----------------------------------------------------------------------

カラム指定

-- COLNAME POSITION(   1:  10) CHAR
--abcdefghijklm... => 'abcdefghij'
-- COLNAME POSITION(   1:  10) DECIMAL EXTERNAL
-- 1234567890... => 123456789
-- COLNAME POSITION(   1:  10) ZONED(10,3)
-- 1234567890... => 123456.789
-- COLNAME POSITION(   1:  10) DATE "YYYY/MM/DD"
--2007/07/07... => 2007-07-07
-- COLNAME POSITION(   1:  10)
--
-- COLNAME CHAR TERMINATED BY ','
--カンマ区切りの項目
-- COLNAME CHAR TERMINATED BY ','  ENCLOSED BY '"' AND '"'
--カンマ区切りの項目(囲み文字あり)
-- COLNAME CHAR TERMINATED BY '\t'
--タブ区切りの項目
-- COLNAME CHAR TERMINATED BY '\t' ENCLOSED BY '"' AND '"'
--タブ区切りの項目(囲み文字あり)
-- COLNAME CONSTANT 100
--固定値で 100 をセット

日付マスクを使用して日付データをロードする

PROC_DATE  POSITION(1,10) DATE "yyyy/mm/dd"

SQL文字列を使用して日付データをロードする

PROC_DATE  POSITION(1,10) "TO_DATE(:PROC_DATE, 'yyyy/mm/dd')",

SQL文字列を使用して日付データをロードする(年度項目付き)

PROC_DATE  POSITION(1,10) "TO_DATE(:PROC_DATE, 'yyyy/mm/dd')",
PROC_NENDO                "TO_CHAR( ADD_MONTHS( TO_DATE(
                          :PROC_DATE, 'yyyy/mm/dd'), -3), 'yyyy')",

SQL文字列を使用してスペースの時NULLに置き換えて格納

PROC_DATE  POSITION(1,10) "TO_DATE(:PROC_DATE, 'yyyy/mm/dd')"
                          NULLIF PROC_DATE=BLANKS,
PROC_NENDO                NULLIF PROC_DATE=BLANKS,
                          "TO_CHAR( ADD_MONTHS( TO_DATE(
                          :PROC_DATE, 'yyyy/mm/dd'), -3), 'yyyy')",

最終更新時間：2008年11月14日 15時43分00秒　指摘や意見などあればSandBoxのBBSへ。

INDEX