!!!Oracle SQL*Loader {{category Oracle,nolink}}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')",