SQL*Loader 〜固定長データのローディング〜

SQL*Loader

〜固定長データのローディング〜

０．改定履歴

2000.09.16　初版

１．はじめに

　このドキュメントは，Oracle8にて、SQL*Loaderを利用する方法の説明を行う．

２．SQL*Loaderとは？

　SQL*Loaderは，コマンドラインで動作するツールで，テキストのデータをOracleのテーブルに取り込むことができる．

　主な目的は，次のようなことが考えられる．

他のシステムのデータベースやファイルをOracleに移行する場合
データウェアハウスシステム等で定期的にテキストデータを取り込む場合
Excelなどのツールで作成されたデータを取り込む場合

　テキストファイルは，固定長のデータで構成されるものと，カンマなどの区切り文字で指定された可変長フィールドの取り込みが可能である．

２．１．利用場所についての補足

　SQL*Loaderは，データベースが稼動しているマシンでしか動作できない．（たぶん）

　それは、Oracleへ接続する方法として，IPC接続を行っているためで，これは他の方法（TCP/IPを使うなど）を利用する場合のオーバヘッドをなくすためと思われる．

２．２．ファイルについて

　SQL*Loaderの動作には，色々な周辺ファイルが必要である．　以下にそれを示す．

オプション	拡張子	内容
control	.ctl	取り込むデータのフォーマットやロード対象となるテーブル名などのロード方法の記述されたファイル．
log	.log	実行時にどのような動作をしたかが記録されるファイル．
bad	.bad	ロードさらなかったデータが格納される．
data	.dat	ロードされるデータが格納されているファイル．
discard	.dsc	廃棄されたデータが格納される．

　この中でも実行時に必要なのは，コントロールファイルとデータファイルである．

　その他のファイルは，実行時に結果が収録されたりするもので，指定がなければ作成されないものもある．

３．テスト用テーブルの作成

　この資料のテスト用として，テーブルを作成する．

SQL*Plusを起動し，scott/tigerでログインする．
次のようにテーブルを作成する．

SQL*Plus: Release 8.0.5.0.0 - Production on 土 Sep 16 18:47:4 1999

(c) Copyright 1998 Oracle Corporation.Allrights reserved.

Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
に接続されました。
SQL> create table TestTable
2 (
3 FIELD1 NUMBER(5) PRIMARY KEY,
4 FIELD2 VARCHAR2(10),
5 FIELD3 DATE
6 )
7 ;

表が作成されました。

SQL>　commit;

コミットが終了しました。

SQL>

４．ファイル構成の説明

４．２．制御ファイルの作成

　制御ファイルは，次のような構造となっている．

制御ファイルの項目
項目	説明
`LOAD DATA`	制御ファイルの先頭に必ず定義する．
`INFILE`	ロードするデータファイルを指定する．
`モード`	データロードのモードを指定する．
`INTO TABLE`	ロード先のテーブル名を指定する．
フィールド指定	データファイルのデータ位置と，テーブル上のどの項目に割り当てるか，またその収録形式などを設定する．データの開始位置は，01から始まる．

　データロードのモードは，次の４つが用意されている．

モードの説明
モード	動作
`INSERT`	データの無いテーブルにデータを挿入する．　既にデータがある場合はエラーとなる．
`APPEND`	データを追加する．　テーブルに既にデータが存在する場合，新規データのみが追加される．
`REPLACE`	テーブルの行が削除され，新規にデータがロードされる．
`TRUNCATE`	テーブルの行が削除され，新規にデータがロードされる．　対象の表にて，参照整合性制約を設定している場合は，それをオフにしておく．

メモ帳で，次のように制御ファイルを作成する．

load.ctl
LOAD DATA INFILE 'data.dat' INSERT INTO TABLE TestTable ( FIELD1 POSITION(01:05) INTEGER EXTERNAL, FIELD2 POSITION(06:15) CHAR, FIELD3 POSITION(16:23) CHAR "to_date(:FIELD3,'YYYYMMDD')" )

４．２．データファイルの作成

　データファイルは，単純なデータの並びとなっており，データの1行がテーブルの1行に相当する．

メモ帳で，次のようにデータファイルを作成する．

data.dat
11111123456789020001025 22223333333333320001025 33334444444444420000926 55555666666666619701025

５．SQL*Loaderの利用テスト

５．１．INSERTのテスト

コマンドプロンプトを起動し，SQL*Loaderを実行する．
- 今回はOracle8を使っているので，SQL*Loaderの名前は"SQLLDR80.EXE"となっている．

D:\>sqlldr80 scott/tiger control=D:\load.ctl

SQL*Loader: Release 8.0.5.0.0 - Production on 土 Sep 16 23:4:5 2000

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

コミット・ポイントに達しました。 - 論理レコード件数:4

D:\>

SQL*Plusで，データをロードしたTestTableをselectしてみる．

SQL> select * from TestTable;

   FIELD1 FIELD2     FIELD3
--------- ---------- --------
    11111 1234567890 00-10-25
    22223 3333333333 00-10-25
    33334 4444444444 00-09-26
    55555 6666666666 70-10-25

SQL>

SQL*Loaderを実行したディレクトリに，load.logファイルが作成されているので，それを開いてみる．

SQL*Loader: Release 8.0.5.0.0 - Production on 土 Sep 16 23:7:46 2000

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

制御ファイル:     D:\load.ctl
データ・ファイル: data.dat
  不良ファイル:   D:\data.bad
  廃棄ファイル:   指定なし
  (レコード拒否はいくつまでも許します。)

ロード数:         ALL
読み飛ばす数:     0
許容エラー数:     50
バインド配列:     64 行、最大 65536 バイト
継続文字:   指定なし
使用パス:      従来型

表: TESTTABLE、ロード済(すべての論理レコードから)
この表に対しての有効なインサート・オプションはINSERTです。

   列     名                    位  置    長さ  区切 囲み   データ型
------------------------------ ---------- ----- ---- ---- ---------------------
FIELD1                                1:5     5           CHARACTER            
FIELD2                               6:15    10           CHARACTER            
FIELD3                              16:23     8           CHARACTER            

列: FIELD3には適用されたSQL文字列
"to_date(:FIELD3,'YYYYMMDD')"
がありました。

表「TESTTABLE」:
  4 行ロードできました。
  0 行はデータ・エラーによってロードされませんでした。
  0 行はWHEN句のエラーによってロードされませんでした。
  0 行件は各列が空のためロードされませんでした。

バインド配列に割り当てられた領域:    65520 バイト(2184 行)
その他に割り当てられた領域:              0 バイト

読み飛ばされた論理レコードの合計:            0
読み込まれた論理レコードの合計:              4
拒否された論理レコードの合計:                0
廃棄された論理レコードの合計:                0

実行開始:  土 Sep 16 23:07:46 2000
実行終了:  土 Sep 16 23:07:48 2000

実行時間:         00:00:01.58
CPU時間 :         00:00:00.15

５．２．REPLACEのテスト

制御ファイルを開き，INSERT文をREPLACE文に書き直す．

REPLACEに変更した制御ファイル(抜粋)
LOAD DATA INFILE 'data.dat' REPLACE INTO TABLE TestTable

データファイルを開き，全てのデータを消して，次のデータを入力して1行だけのファイルとする．

11111222222222220001025

SQL*Loaderを先程と同じように実行する．
SQL*Plusでテーブルの内容を確認する．

SQL> select * from TestTable;

   FIELD1 FIELD2     FIELD3
--------- ---------- --------
    11111 2222222222 00-10-25

SQL>

REPLACEなのに，1行だけ置き換わるのではなく，前レコードが削除された後に追加されている事が確認できる．

５．３．APPENDのテスト

制御ファイルを開き，REPLACEをAPPENDと書き直す．
データファイルを開き，次のように入力する．

33333222222222220001025

SQL*Loaderを先程と同じように実行する．
SQL*Plusでテーブルの内容を確認する．

SQL> select * from TestTable;

   FIELD1 FIELD2     FIELD3
--------- ---------- --------
    11111 2222222222 00-10-25
    33333 2222222222 00-10-25

SQL>

行が追加されていることが確認できる．

６．色々なデータのロードの方法

これまで利用してきたテーブルを削除し，新しく項目を追加したテーブルを作成する．

SQL> drop table TestTable;

表が削除されました。

SQL> create table TestTable
  2  (
  3  FIELD1 NUMBER(5) PRIMARY KEY,
  4  FIELD2 VARCHAR2(10),
  5  FIELD3 DATE,
  6  FIELD4 NUMBER(3),
  7  FIELD5 CHAR(1)
  8  );

表が作成されました。

SQL> commit;

コミットが完了しました。

SQL>

制御ファイルを次のように修正する．

LOAD DATA
INFILE 'data.dat'
INSERT
INTO TABLE TestTable
(
   FIELD1       POSITION(01:05)    INTEGER EXTERNAL,
   FIELD2       POSITION(06:15)    CHAR ,
   FIELD3       POSITION(16:23)    CHAR "to_date(:FIELD3,'YYYYMMDD')",
   FIELD4       POSITION(24:25)    INTEGER EXTERNAL ":FIELD4*100",
   FIELD5                          CHAR "0"
)

データファイルを次のように修正する．

111112222222222197010251
222223333333333196209262
333334444444444194505293

SQL*Loaderを実行し，SQL*PLusで中身を確認する．

SQL> select * from TestTable;

   FIELD1 FIELD2     FIELD3      FIELD4 F
--------- ---------- -------- --------- -
    11111 2222222222 70-10-25       100 0
    22222 3333333333 62-09-26       200 0
    33333 4444444444 45-05-29       300 0

SQL>

　このロードでは，制御ファイルにて演算と固定値を設定している．

　FIELD4は，データファイルから読み込んだデータを演算して結果を入力している．

　FIELD5は，あらかじめ制御ファイルで設定した値が挿入されていることがわかる．