【Oracle】SQL Loaderコントロールファイルの内容を生成するスクリプト
以下のSQLにバインド変数「target_tab_name」を与えると、
コントロールファイルの中身に相当する内容をsqlで返してくれる。
結果をコピー⇒ペーストすればコントロールファイルの出来上がり。
SELECT
*
FROM
(
-- bindsize,readsizeは64M指定
SELECT
N'options(rows=-1,errors=-1,skip=1,bindsize=67108864,readsize=67108864)' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
SELECT
N'LOAD DATA' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
-- 入力ファイルは[テーブル名]_data.tsv、改行コードはCRLF
SELECT
N'INFILE ''' || :target_tab_name || '_data.tsv''' || ' "str ''\r\n''"' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
SELECT
N'APPEND' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
SELECT
N'INTO TABLE ' || :target_tab_name AS CONTROL_FILE_STR
FROM
dual
UNION ALL
-- 入力ファイルの項目区切り文字はTAB（X09）
SELECT
N'FIELDS TERMINATED BY X''09''' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
SELECT
N'TRAILING NULLCOLS' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
-- 項目マッピング
SELECT
N'(' AS CONTROL_FILE_STR
FROM
dual
UNION ALL
SELECT
TO_NCHAR(
' ' ||
CASE
WHEN a.COLUMN_ID > 1 THEN ',' ELSE ' '
END ||
a.COLUMN_NAME ||
-- DATE型ならyyyy/MM/dd HH24:mi:ss、TIMESTAMP型ならyyyy/MM/dd HH24:mi:ss.FF3形式で編集
CASE
WHEN a.DATA_TYPE = 'DATE' THEN ' "TO_DATE(:' || a.COLUMN_NAME || ',''yyyy/MM/dd HH24:mi:ss'')"'
WHEN a.DATA_TYPE LIKE 'TIMESTAMP%' THEN ' "TO_TIMESTAMP(:' || a.COLUMN_NAME || ',''yyyy/MM/dd HH24:mi:ss.FF3'')"'
ELSE ''
END
) AS CONTROL_FILE_STR
-- FROM句にUSER_TAB_COLUMNSだけを指定して後でorder byさせるとなんかうまく動作しないので
-- 副問い合わせ内で先にorder byした後で使用する
FROM
(SELECT * FROM USER_TAB_COLUMNS WHERE table_name = :target_tab_name ORDER BY column_id) a
UNION ALL
SELECT
N')' AS CONTROL_FILE_STR
FROM
dual
);
以下はコメントにも記述しているが注釈。
- bindsize,readsizeは64M指定になっている。
- 入力ファイルは
テーブル名_data.tsv、改行コードはCRLF。改行コードがLFになる場合は"str ''\r\n''"の部分を空文字にしてしまえばよい。 - badfile等の指定は省略しているので、このままやるとコントロールファイルと同階層にbadfileが出力される。
- 入力ファイルの項目区切り文字はTAB（X09）、つまりTSVファイルを想定している。これは個人的にTSVのほうが使い勝手がいいからという程度の理由なので実態に合わせてある程度変更する必要も出てくるだろう。
- 項目マッピングに関しては、
USER_TAB_COLUMNSのCOLUMN_IDの昇順に並んでいることが前提になっている。入力ファイルもこれに合わせて作成しておく必要がある。 - 登録元のテーブルにDATEもしくはTIMESTAMP型が存在する場合は、入力ファイルのその項目に相当する値に対して日付書式変換をかける指定をしている。DATE型なら
yyyy/MM/dd HH24:mi:ss(javaでいうとyyyy/MM/dd HH:mm:ss)、TIMESTAMP型ならyyyy/MM/dd HH24:mi:ss.FF3(javaでいうとyyyy/MM/dd HH:mm:ss.SSS) 逆に言うとこれにマッチしない場合は全部エラーではじかれるので注意。
