SQL*Loader（日文版）

SQL*Loader

CSVファイルからOracleのテーブルへデータを流し込むツール。
大量のinsert文を発行するよりは、断然高速。

データであるCSVファイルや固定長ファイルと、ロード方法を指定するコントロールファイルを用意して実行する。

（CSVファイルからのロードはこのSQL*Loaderが使えるが、CSV出力には標準的な方法は無いらしくて、select文で加工する方法がよく使われるらしい。このSQL文をいちいち書くのは少々面倒なので、SQL生成用Excelマクロを作ってみました）

---

コントロールファイル

CSVファイルの各項目とテーブルの項目との関連付け等を指定する。
（コントロールファイルをテキストエディタで書くのはけっこう面倒なので、コントロールファイル作成用Excelマクロを作ってみました（CSVファイル用、固定長ファイル用））

例）emp.ctl：

OPTIONS(LOAD=100,SKIP=1,ERRORS=-1,ROWS=10)
LOAD DATA
INFILE 'data/emp.csv'
BADFILE 'emp.bad'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
)

キーワード	説明		備考
OPTIONS	sqlldrに渡す引数を、コントロールファイルの中に記述できる。[2004.11.15]		たぶん、「sqlldr -?」で出てくるオプションを指定できる。
	LOAD	ロードするレコード数	-1の場合、全て
	SKIP	スキップするレコード数	-1の場合、4294967295（0xffffffff）
	ERRORS	許容するエラーの数	-1の場合、全て
	ROWS	何件毎にコミットするか	-1の場合、4294967295（0xffffffff）
LOAD DATA	お約束の記号（？）
CHARACTERSET	文字コードを指定したい場合に指定する。[2005.3.18]		例：「CHARACTERSET JA16SJIS」 JA16EUC、UTF8、JA16DBCS（EBCDIC）等
INFILE	入力データであるCSVファイルや固定長ファイル		sql*loaderを実行したディレクトリからの相対パスでディレクトリを指定可能。
BADFILE	何らかのエラーがあってDBに入れられないデータがあった場合、そのデータがこのファイルに出力される。
DISCARDFILE	WHENによってロード対象外となった廃棄データが、このファイルに出力される。[2005.3.18]
INSERT APPEND REPLACE TRUNCATE	以下のいずれかのモードを指定する。
	INSERT	新規にデータをロードする。	テーブルは空である必要がある。既にデータがある場合はエラーとなる。（重複しないデータであっても！）
	APPEND	データを追加する。	既にデータがある場合は、duplicateしないデータだけが追加される。
	REPLACE	テーブルの内容を全て削除し、新規にデータをロードする。	削除は、DELETEに相当。
	TRUNCATE		削除は、TRUNCATEに相当。truncateできる権限が必要。参照整合性制約を設定している場合は、それをオフにしておくべき。
INTO TABLE	データを入れるテーブル
WHEN	データを入れる条件。SQLのWHERE句と同様の書き方。 この条件によって廃棄されたデータは、廃棄ファイルに出力される。[2005.3.18]		例：「WHEN 列名=値」「WHEN (列名>=値) AND (列名<=値)」
FIELDS	項目の区切り方の指定。
	TERMINATED BY	データを区切る文字を指定。	カンマ区切りの場合は「TERMINATED BY ","」 タブ区切りにしたい場合は「TERMINATED BY X'09'」 固定長ファイルの場合は不要
	OPTIONALLY ENCLOSED BY	データを囲む文字を指定。囲まない場合は不要。	ダブルクォーテーションで囲む場合は「OPTIONALLY ENCLOSED BY '"'」
TRAILING NULLCOLS	この指定があると、データの無い項目にNULLを入れる。

コントロールファイルの中で、「--」で始まっている行は コメント扱いになる。

---

各項目の後ろには、関数を書いて演算をすることも出来る。

  MGR,
  HIREDATE "TO_DATE(:HIREDATE,'YYYY/MM/DD HH24:MI:SS')",
  SAL,

この際、関数の引数に書く項目名は「:（コロン）」を付ける事。これを忘れるとORA-00984に悩むことになる。

---

また、項目毎にファイル内のデータの属性を指定することも出来る。これは特に固定長ファイルの場合に重要。

属性	説明	DBの属性	指定例	データ例	DBに入るもの
CHAR	文字列	char、varchar2	CHAR	hoge	hoge
DECIMAL EXTERNAL	数値	number	DECIMAL EXTERNAL	123	123
ZONED	数値（小数扱い）	number	ZONED(7,2)	1234567	12345.67
DATE	日付。書式を後ろに付ける	date	DATE "YYYYMMDD"	20041030	2004-10-30
CONSTANT	定数（ファイル内のデータを使わない）	何でも	CONSTANT 100		100

空白のみの項目は やはり空文字列として扱われ、nullにはならない。nullを入れたい場合は以下のような工夫が必要。

  SAL POSITION( 36 : 42 ) ZONED(7,2),
  COMM POSITION( 43 : 49 ) CHAR "decode(:COMM,'',null,to_number(:COMM))",
  DEPTNO POSITION( 50 : 51 ) DECIMAL EXTERNAL

---

データファイル

固定長ファイルも可能だが、よく使われるのはCSV形式のファイルだと思う。
データファイルの名前は、コントロールファイル内に記述する。

コントロールファイルで指定した項目数よりCSVファイル側の項目数が多い場合は、無視されるだけで問題ない。

Windowsのテキストファイルの場合、ファイルの最後にEOFのコードが付いている場合がある。
この行は（属性が不一致であれば）エラーとなり、ロードされない。（badファイルに出力される）

---

実行

sqlldrの引数にコントロールファイルの名前を指定して実行する。

Windowsの場合、バッチファイルを作っておくと便利。このファイルをダブルクリックするとロードが実行される。ネットワークドライブ上では駄目っぽかったけど。

例）emp_load.bat：

C:/oracle/ora92/BIN/SQLLDR ユーザー/パスワード@SID control=ctl/emp.ctl
pause

実行すると、バッチファイルと同じディレクトリにログファイルが出来る。

C:/sample>tree /f
フォルダ パスの一覧
ボリューム シリアル番号は 71EFE346 B876:91FD です
C:.
│  emp.bad
│  emp.log
│  emp_load.bat
│
├─ctl
│      emp.ctl
│
└─data
        emp.csv