Oracle 　 SQL*Loaderミニミニメモ
　 2002/01/10 - 2005/04/15

タブ区切りデータ用の制御ファイル例

Loader制御ファイルでタブ文字を表すには X'09' とします。以下が例です。 


LOAD DATA
INFILE '../data/users.dat'
REPLACE
INTO TABLE USERS
WHEN (EMPNO <> BLANKS)
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  EMPNO, USERNAME, KANJI_NAME, MAIL_ADDRESS, TELNO,
  SECTION_NAME, JOB_NAME,
  DDATE CHAR
    "DECODE(NVL(:DDATE, ' '), ' ', ' ',
            TO_CHAR(TO_DATE(:DDATE, 'YYYY-MM-DD'), 'YYYYMMDD'))",
  CDATE DATE "YYYY-MM-DD HH24:MI:SS"
)



列の名前に続けてデータ型、その後にダブルクォートで囲んで SQL関数式を書くことができます。その式の中で「:列名」とすると、列参照を行うことができます。 また、"CDATE"の定義のように、DATE型のカラムで書式が決まっている場合には、 書式を二重引用符で囲んで表現します。 その上の"DDATE"の定義は分かりにくいですがこれはCHAR2(8)のカラムです。 データファイルにYYYY-MM-DDの文字列で書かれているのを、 セットされていたらYYYYMMDDに変換して、そうでなければ空白をセットします。
タブ区切りとNULL

タブ区切りテキストでNULLデータがある場合、タブ文字が複数個連続します。 しかし、このとき「OPTIONALLY ENCLOSED BY」句をつけていると、 複数個のタブが1個と認識され、データが足りないというエラーになります。 NULLを取り得るカラムと引用符で囲まれる可能性があるカラムが完全に別の場合は、 OPTIONALLY ENCLOSED BY句を必要なカラムにだけつけるとOKです。 


...
QUANTITY1 CHAR OPTIONALLY ENCLOSED BY '"',
...


カンマを含む数値データ

もうひとつ、よくある例が、Excelなどでタブ区切りテキストを作成するときに、 数値データを3桁毎にカンマで区切っている例です。このような場合は、 カラムの定義を次のようにします。 


...
QUANTITY1 CHAR OPTIONALLY ENCLOSED BY '"' "TO_NUMBER(:QUANTITY1, '999,990.000')",
...


カンマ区切りデータ用の制御ファイル例

カンマ区切りでも基本は同じです。 今度の例ではREPLACEの代わりにAPPENDを使っているので、 既存の行は削除せず追加しようとします。 「PRESERVE BLANKS」はカンマの前後の空白も削除せずに、 データの一部とみなす指令です。 


LOAD DATA
INFILE '../data/users.dat'
APPEND
PRESERVE BLANKS
INTO TABLE USERS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  EMPNO, USERNAME, KANJI_NAME
)


固定長データ用の制御ファイル例

最後に固定長の場合です。下のように、 


LOAD DATA
INFILE '../data/users.dat'
APPEND PRESERVE BLANKS
INTO TABLE USERS
(
  EMPNO	        POSITION(1:10)    ZONED,
  KANJI_NAME    POSITION(11:30)   CHAR,
  T_DATE        POSITION(31:38)   DATE "YYYYMMDD",
  SURYO	        POSITION(47:53)   ZONED(7,1),
  SECTION_NAME  POSITION(54:75)   CHAR,
  H_DATE        POSITION(86:89)   CHAR
    "DECODE(:H_DATE, '    ', '    ',
 DECODE(SUBSTR(:H_DATE, 1, 1), '9', '19', '20') || :H_DATE)",
  JOB_NAME      CONSTANT 'UNDEFINED',
  PROCESS_CODE  CONSTANT 'A'
)



数値はZONED、文字列はCHARなどを使います。 日時型は上のように、書式を添えて指定することになるでしょう。 定数はCONSTANT句で定義できます。
文字コードについて

SQL*Loaderのデータファイルの文字コードは、環境変数NLS_LANGで制御できます。 ですから、内部コードが日本語EUCのOracleデータベースに、 シフトJISのデータファイルからデータを投入することも可能です。

UNIX上のOracleで注意が必要なのは改行コードです。 データファイルの改行コードがCR+LF(Win32形式)だと、 うまくいきません。FTPの際に必ず改行コードをLF(UNIX形式) に変換しておく必要があります。
日付データ

SQL*Loaderで固定の日付や現在の日時をカラムにセットするには、 次のようにします。"CHAR" が分かりづらいですね。 


DATE1 CHAR "TO_DATE('20030331', 'YYYYMMDD')",
DATE2 SYSDATE,


初期値を設定する

特定のカラムの値がNULLの場合に、初期値を自動的に設定する DEFAULT句のようなものはありませんが、 DECODE句を使ってそのような機能を実現することはできます。 


T_CODE POSITION(1:8) CHAR "DECODE(:T_CODE,NULL,'00000000',:T_CODE)",


　 もくじ
Oracle 　 (C) 2002 Project Radkraft ( MISUMI URANO )