SQL Loader ユーティリティ FAQ
COMMIT のタイミング
SQL*Loader には インポートユーティリティのように COMMIT=Y のようなオプションはなく、
バインド配列 (bindsize オプション、rows オプション) を処理する度に COMMIT が発行される(従来型パスの場合)。
また途中で終了する場合でも commit_discontinued オプションによって制御することが可能。
CSV フォーマットと改行を含むデータ
SQL*Loader ではストリーム・レコード形式による 可変長の CSV ファイル を使用しての二重引用符(") のクォート内部で改行するデータは基本的に取り扱うことができない。
CONTINUE 〜(NEXT、THIS など) 文を使用することによってトリッキーに取り込むことは可能ではあるが、入力データの編集が必要になるため、固定長のレコードとして処理することが実装時における現実的な手法だと思われる。
ストリーム・レコード形式
一般的な可変長のレコードとして扱われているものは、SQL*Loader にとってはストリーム・レコード形式に該当する。
(SQL*Loader にとっての可変レコード形式は、レコードの先頭の数バイトに、そのレコード長を指定しているものである。)
代表的なストリーム・レコード形式は、改行コードをレコード終了記号とするレコードサイズが可変長の形式
ちなみに SQL*Loader におけるレコード形式は以下の３種類
- 固定レコード形式 (例： INFILE file_name "FIX n" )
- 可変レコード形式 (例： INFILE file_name "VAR n" )
- ストリーム・レコード形式 (例： INFILE file_name "STR '\n'" )
そしてストリーム・レコード形式の多くはフィールドセパレータをカンマ(,)に設定し、(任意に使用される)囲い文字 を二重引用符(") にすることが多い。＝代表的な CSV フォーマット
- CR+LF をレコード区切り文字列、フィールドセパレータをカンマ(,)、(任意)囲い文字を二重引用符(")にする例
LOAD DATA
INFILE file_name "str '\r\n'"
[APPEND | REPLACE | TRUNCATE ] INTO TABLE xxx
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
SQL*Loader では単一引用符をクォートに使用する場合には OPTIONALLY ENCLOSED BY '\'' と \ マークで単一引用符(')をエスケープする。
但し、この方法は 外部表 では使用できない。
外部表では SQL*Loader と仕様が異なり単一引用符(')をクォートに定義するときに
OPTIONALLY ENCLOSED BY '\'' が使用できない。 ENCLOSED BY "'" と二重引用符(")で単一引用符(')を囲うようにする。
プラットホームによるレコード終了記号の設定
INFILE file_name ["str terminator_string"]
- SQL*Loader を実行する OS が UNIX 系の場合のデフォルトで LF(\n) がレコード終了記号として使用される。
- Windows 系の場合のデフォルトは一定ではなく LF(\n)、CR+LF(\r\n) のどちらかが先に登場すると、
それがレコード終了記号として採用される。
データエントリの各モード時の注意点 (xxx INTO TABLE 句)
SQL*Loader のデータエントリには APPEND, REPLACE, TRUNCATE のモードがある。
- APPEND
- APPEND INTO TABLE
追記モード(デフォルト)。パラレル実行の場合には、APPEND モードしか利用できない。
- REPLACE
- REPLACE INTO TABLE
削除モード。重複データの置き換えではなく、全レコードの DELETE を行ってからローディング処理を行なう。
TIPS: ステージング領域(中間テーブル)無しにレコードデータの置換処理をしたい場合には、外部表と MERGE を使用する。
- TRUNCATE
- TRUNCATE INTO TABLE
切捨てモード。TRUNCATE TABLE を行なってからローディング処理を行なう。
テーブルへの DELETE 権限では、この設定は利用することができない点に注意する。
ORA-01031: 権限が不足しています。 のエラーになる
(TRUNCATE TABLE には DROP TABLE 権限が必要)
漢字コード・半角カタカナ (プラットホームとキャラクタセットの違い)
異なるプラットホームの異なるシステムからのデータを入力データとして使用する場合、データの入手過程の通信経路でコード変換をするべきか、しないべきかを迷うことがある(SQL*Loader は Oracle Net 経由でも使用できる)。
- コード長の違い
例えば、入力ファイルのデータがシフト JIS、データベースキャラクタセットが JA16EUCTILDE(EUC-JP)の場合、
半角カタカナのバイナリのコード長が異なる。シフト JIS では１バイト、EUC-JP では２バイト長となる。
- 改行コードの違い
また、プラットホームによって改行コードが CR+LF の場合(主に Windows 系) と LF (主に UNIX 系) のように
長さが異なる点にも気を使う必要がある。
- POSITION 句のセマンティクス
POSITION(nnn:mmm) などを使用している場合、このときの位置指定はセマンティクスの設定に関わらず、常にバイト指定になる。（Oracle 10g 時点）
データファイルに固定長を想定している場合、作成したオリジナルデータのコード変換すると文字列長が変化し、データレコードが固定長ではないという可能性が発生する。
このようなケースは FTP などの ASCII モードや nkf などの変換ツールを使用してはいけない。SQL*Loader における CHARSET 指定におけるコード変換を使用する。
ストリーム形式の場合にはカラムの定義の大きさに十分な余裕があれば、このような問題に気を使わずに済む。しかし、固定長のレコードには、パラレル処理による処理速度の向上、トークン切り出し処理や誤データの検出処理が容易、改行データの取り扱いなどのメリットが多い
(ホワイトスペースによるパディング処理によってデータファイルのサイズは大きくなるデメリットもある)。
文字属性 (CHAR) の１カラムの長さの上限
バインドする変数の上限は 4000 バイトまでだが…ストリーム形式で区切り文字を使用した文字属性のデフォルトの上限は 255 バイト（区切り文字なしでは１バイト）。255 バイトを超える場合には CHAR(4000) などのようにデータサイズを宣言するか POSITION を指定しなければデータ読み込み時にエラーとなる。
また、ストリーム形式の単一データが 4000 バイトを超える文字型の一部を「文字型」として抽出したい場合には固定長フォーマットにて POSITION で切り捨てするか、あきらめて LOB にする。
不良ファイルと拒否ファイル
不良ファイル (badfile) には入力データを Oracle にインサートしようとしてできないデータが出力される。
ダイレクトパス か 従来型パスかによって変化するがデータフォーマットの不一致、制約違反、型変換の失敗。
拒否ファイル (discard) にはコントロールファイル内で処理されなかったデータが出力される。
コントロールファイル内の WHEN 句の分岐により処理されなかったレコード。
つまり、拒否ファイルが出力されることはコントロールファイルの制御下にある警告のようなログファイルであるが、不良ファイルが出力されることはデータファイル、または、一意制約などに関わるデータの品質に由来するエラーログに近い状態であるとも言える。
- badfile と discardfile のファイルはともに該当するレコードが無い場合には出力されない。
- ファイルは上書きモードを出力される。
