ハニカムウエアのブログ

ハニカムウエアのブログ

システム開発、セキュリティ対策についての記事を書いています。

Oracle tips:SQL*Loader 制御ファイル、外部表作成における文字コードの注意事項

Tips Oracle

こんばんは、インフラ担当のロボ2号です。
最近、Oracle12c で少し嵌った事がありましたので、備忘録もかねてご紹介します。

# 制御ファイルと外部表を作成する SQL文の文字コード

ある案件で SQL*Loader を使用する機会があり、入力ファイルはUTF8でした。
データベースの文字コードセット(DB CHARACTERSET)はJA16EUCでしたが
ファイルサイズが巨大なため、変換は Oracle に任せることとしました。

# SQL*Loader が動かない！

で、制御ファイルを作成してテストをしたところ、いくつかのテーブルですが
SQL*Loader がエラーも返さず固まるという事象に出くわしました。
プロセスのシステムコールを見ても何かを待っている様子で処理がずっと
ループしており、Oracle の専用サーバプロセスを見ても
SQL*Net message from client イベントで待機しており応答待ちになっていました。

Oracle に詳しい方に相談したところ、このような現象は
はじめてとの事でしたが日本語(マルチバイト)があるので表名や列名は "" で括る方が
安全ですよ、とアドバイスを頂いたので早速変更しました。(そりゃそうですよね)

# 少し前進

早速変更したところ、処理は動くようになりましたがあいかわらず
エラーが出て処理ができません。その日はあきらめて早々に帰宅しました。
翌日、もう一度眺めてみたところ、おかしな現象が出るテーブルにはある共通点が
ありました。

# UTF8 と EUC では使える日本語の文字数が異なる

それは、テーブル名や列名に「11文字以上の日本語」があるテーブルでのみ
処理がおかしくなっていた、というものでした。制御ファイルもなぜか
UTF-8 で作っており、環境変数の NLS_LANG を JAPANESE_JAPAN.AL32UTF8 と
していたせいで、日本語をテーブル名や列名に設定する際の「文字数」が
10文字に制限される事に気が付いていませんでした。orz

内部的な処理までは分かりませんが、"" がない場合に固まるのは
テーブル名、列名を格納する変数に対して制御ファイルで指定している日本語部分が
バッファを超えてしまい、動きがおかしくなっていたのかもしれません。
"" で括ることによって、読み込みまでは動くものの、UTF-8 で許容される長さを
超えたテーブル名や列名があるため、やはり処理がおかしくなっていたのではないでしょうか。

# 外部表の定義でも同様の注意が必要

外部表の作成にあたっては列定義の他に ACCESS PARAMETERS 句の
FIELDS パラメータにも列の名称を記載します。この SQL文にもなぜか
UTF8 のファイルを使用して NLS_LANG=JAPANESE_JAPAN.AL32UTF8 として
処理していたせいで、SQL*Plus から外部表をアクセスした際に
本来は適切に読み込める筈の列でエラーが起きて、大量に bad ファイルが
出力されていました。

# まとめ

・SQL*Loader 制御ファイル、外部表作成のSQL文の文字コードは
　DB_CHARACTERSET と同じにしましょう。
　せめてマルチバイト文字のバイト数が同じ文字コードセットにすべきです。
・使用する OS の環境にあわせてやむなく変える場合、表や列の定義に
　マルチバイト文字を使用しているときは、AL32UTF8 だと
　使用できる文字数が減ることで、処理が動かなくなる可能性があります。

なぜか制御ファイルも UTF8 で作成したばっかりに
無駄な苦労をする羽目になりました。(^_^;

普通の方は私みたいに変な事をしないので、このような羽目にならないとは思いますが
Oracle 12c をご利用の際、同じ目に逢わぬようにご注意くださいね。

参照：
SQL*Loader制御ファイル
Oracle® Databaseユーティリティ 12c リリース1 (12.1.0.2) B71303-05
　http://docs.oracle.com/cd/E57425_01/121/SUTIL/toc.htm
　目次から、下記の節をご覧ください。
　　7 SQL*Loaderの概念 ⇒ SQL*Loader制御ファイル
　　15 ORACLE_LOADERアクセス・ドライバ



それではまた！