SQL*Loaderの使い方

SQL*Loaderの使い方です。コマンドは以下のようにします。

controlでコントロールファイルを指定します。

dataでインタフェースファイルを指定しますがこのデータがテーブルにinsertするデータになります。

badで指定するファイルはinsertに失敗したデータがログとして出力されます。

logで指定するファイルはログファイルです。

direct=TRUEと指定すると、SQL*Loaderの処理が早くなるので普通は記述しますが、いくつか制約もあります。

  • クラスタ化された表には使用できない
  • データロード中、表ロックがかかる
  • データロード時、外部キー制約、チェック制約が無視される

そのため、PKを指定しても重複したデータが登録できてしまいます。SQLエディタ(A5M2など)で重複データを削除しようとすると、ORA-01502エラーが発生するのでtruncateするしかありません。

コントロールファイルについてですが、固定長の場合は以下のように記述します。

SKIP=0でインタフェースファイルの先頭行からデータとして読み込みます。

ERRORSでエラーデータの最大許容量を指定します。

ROWSでコミット単位を指定します。

その後にLOAD DATAと記述します。

TRUNCATEと書くと一旦テーブルをTRUNCATEしてからインタフェースファイルのデータを読み込みます。TRUNCATE以外にもあります。

上記でインサートするテーブル名を指定します。

POSITION(1:10)でインタフェースファイルの1バイト目から10バイト目までをカラム名1に入れるという意味です。

定数をいれたい場合はCONSTANTキーワードを使用します。

数値(NUMBER)を指定したい場合はDECIMAL EXTERNALを指定します。

CSV形式の場合は以下のように記述します。

カンマ区切りの場合は、以下の1文を記述します。

SQL*Loaderを実行すると以下のエラーが出る場合があります。

原因はいくつかありますが、定数を空にするとこのエラーが発生します。これはカラム名にNULLを設定したい場合に下記のように記述したらORA-01009が発生します。NULLを設定したい場合はカラム名自体記述する必要がありません。

CSV形式で、全ての列をテーブルにロードするのではなく、ある特定の列のみロードしたい場合があります。

例えば以下のようなCSVがあるとします。

全部で3列のCSVですが、3列目を読み飛ばしてテーブルにロードしたいとします。

そのような場合はFILLERを使います。以下、コントロールファイルです。

“dummy”としていますが、”a”でも良いですし、ダブルクォーテーションで囲む必要もありません。但し、テーブルに存在する列名を指定すると以下エラーが発生しますので、”dummy”などのありえないカラム名が良いです。

列を読み飛ばしたい場合はFILLERを使うということを覚えておくと便利です。

SQL*Loaderでなるべく複数のテーブルに取り込まない

SQL*Loaderは大量データを取り込む時に高速で取り込めるため便利なツールです。

一般的には他システムからきたIFファイル(csvまたは固定長)を取り込む場合にシェルでSQL*Loaderを使うと思います。

FILLERを使って1列目と3列目をテーブルAに取り込み、2列目と4列目をテーブルBに取り込むような場合は設計を見直すべきで、一旦SQL*LoaderでテーブルCに取り込んでから別のプログラムでテーブルAに取り込み、また別のプログラムでテーブルBに取り込むべきです。

契約番号 顧客番号 料金 A区分
K000001 A0000001 1

このようなCSV形式のデータが他システムから来た場合、料金に「あ」と入っているため不正なデータ(1行)です。しかし先ほどの設計でいくと、2列目と4列目をテーブルBに取り込む場合は正常データとして取り込んでしまいます。

そのため、SQL*Loaderで取り込む場合は一旦一つのテーブルに取り込むべきだと思います。

SQL*Loaderで指定した条件を取り込む

WHENを使用すると、指定した条件のデータのみを取り込むことができます。

INTO TABLE テーブル名のあとにWHENを指定します。

というようにIS NOT NULLは指定できません。この場合以下のようにします。

逆の場合は以下のように記述します。

但し、この場合はなぜか上手くデータが取得できません。= BLANKSのデータを取得することはできないようです。

というように空以外であれば、=は効きます。

SQL*Loaderで取り込むファイルはシェルのforでループしない

ちょっとSQL*Loaderの使い方とは話がそれますが、SQL*Loaderは基本的に大量データをインサートするために使うツールです。

その為、シェルでファイルを1行ずつfor文でループし、バイトチェックなどをすることは絶対にやってはいけません。(シェルのループは時間がかかるため)

どうしてもバイトチェックやCSVの項目数チェックを行いたい場合はawkを使います。

以下、参考までに書きます。$?は成功なら0、失敗なら1を返します。

インタフェースファイルがダブルクォーテーションで囲まれている場合

ダブルクォーテーションなどの区切り文字で各項目が括られてくるインタフェースファイルの場合は、コントロールファイルに、OPTIONALLY ENCLOSED BY ‘”‘を指定します。

TRUNCATEモード

テーブルにロードする前にTRUNCATEしてからロードします。

APPENDモード

テーブルに既存データの有無に関係なくロードします。

ダイレクトパスロード=TRUEにすると主キーが重複したデータが入る可能性があります。

ダイレクトパスロード=FALSEにすると重複したデータはbadファイルに出力されます。

スポンサーリンク
  • このエントリーをはてなブックマークに追加
スポンサーリンク

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA