以下の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
)
要するに文字列をunionしてファイル内のテキスト情報を無理やり生成するような単純なものだが、意外に重宝している。
以下はコメントにも記述しているが注釈。
■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)
逆に言うとこれにマッチしない場合は全部エラーではじかれるので注意。
