UTL_FILEがNLS_LANGで結果不正

先日、業務チームからUTL_FILEで出力したファイルがおかしいという問い合わせがあった。調べてみると、最後の文字がなくなったり、不要な改行が追加されている部分があり、それも文字の種類によって規則性がある、というものであった。環境はOracle19c(Exadata)、データベースのキャラクタセットはJA16SJISである。

結論から言うと、この事象の原因はDBインスタンスを構成する各種プロセスのNLS_LANG環境変数がデフォルトのAMERICAN_AMERICA.US7ASCIIとなっていたことであった。DBサーバのoracleやgridユーザのNLS_LANGをSJISに設定し忘れた訳ではない。以下のように、Grid Infrastructure(GI)にNLS_LANGを設定することにより、当該事象は回避できた。この設定はOCRに書き込まれるので永続的、ただし反映にはインスタンスの再起動が必要である（マルチテナントの場合はPDBではなくコンテナの再起動が必要）。

srvctl setenv database -d dbname -t "NLS_LANG=Japanese_Japan.JA16SJIS"

さて、以下この事象についてもう少し詳しく記載する。UTL_FILEのマニュアルの以下の記述を読んでほしい（特に最後の一文）。

---
PL/SQL Packages and Types Reference, 255.2 UTL_FILE Operational Notes
UTL_FILE expects that files opened by UTL_FILE.FOPEN in text mode are encoded in the database character set. It expects that files opened by UTL_FILE.FOPEN_NCHAR in text mode are encoded in the UTF8 character set. If an opened file is not encoded in the expected character set, the result of an attempt to read the file is indeterminate. When data encoded in one character set is read and Globalization Support is told (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NLS_LANG is set, it should therefore be the same as the database character set.
---

UTL_FILEを使う場合は、NLS_LANGはデータベースのキャラクタセットと一致させる必要がある。そうでない場合の結果は不定(indeterminate)ということである。つまりこれはUTL_FILEの仕様、ということになる。

問題は、ここでいうNLS_LANGとは何か、ということである。例えばsqlplusのクライアントがリスナー経由でDBサーバへ接続し、PL/SQLの無名ブロックでUTL_FILE.PUT_LINE等でファイル出力するケースを考えよう。この場合、実体としてはDBサーバ上のサーバプロセスがファイル出力するため、このサーバプロセスのNLS_LANGを指すと思われる。では、このNLS_LANGはどこから来るのだろうか。普通に考えるとクライアントからの接続要求はリスナーを経由し、リスナーがこのサーバプロセスを生成することから、このリスナーのNLS_LANGが引き継がれると考えられる。リスナーを起動するOSユーザがoracleユーザであれば、その環境変数に指定されたNLS_LANGであると考えられる。11gR2まではGIが無かったので、多くの場合はOSのoracleユーザのNLS_LANGにより、Oracleのプロセス群に設定されるNLS_LANGが決定されていたはずである。そのため、UTL_FILEの上記仕様の制限を意識する必要性はあまりなかったと思われる。

しかし、11gR2以降では話は異なる。多くの場合、DBインスタンスやリスナー等のDBリソースはGIにより管理される。DBインスタンスはsrvctlにより起動停止を制御し、リスナーはGIにより自動起動される。この場合注意が必要なのは、これらのプロセスは、oracleやgridユーザのNLS_LANGの設定如何にかかわらず、デフォルトではAMERICAN_AMERICA.US7ASCIIが設定されるということである。例えoracleユーザやgridユーザのNLS_LANGにJapanese_Japan.JA16SJISしても、DBインスタンスを構成するプロセス、リスナーはNLS_LANG=AMERICAN_AMERICA.US7ASCIIになる、ということである。この状態でUTL_FILEを使うと、上記制約に引っかかる、という訳である。

この事象の回避方法は、上記で述べたとおりsrvctl setenvでDB毎にNLS_LANGを設定するしかないだろう。DBインスタンスの再起動を伴うためそれなりにインパクトがあるが、受け入れるしかない。残念ながら、試した限りではsrvctl setenv listenerだけではこの事象は回避できない（もしかすると単純にリスナープロセスのNLS_LANGだけが原因、ということではないのかもしれない）。上記設定によりDBサーバのプロセス郡のNLS_LANGが変更されることに不安を覚えるかもしれないが、クライアント側に返却される結果についてはクライアント側のNLS_LANGに依存するため、DBサーバのNLS_LANGの影響を受けない。また、リスナー経由しないDB接続(DBサーバでoracleユーザから直接sqlplus / as sysdbaで接続するなど）は、サーバプロセスもoracleユーザで起動されるため、結果そのユーザのNLS_LANGが引き継がれるため影響を受けない。とはいえ、運用中のシステムに対しこれを変更するのは勇気がいることには変わりない。

なお、DBインスタンスを構成する各種バックグラウンドプロセスが出力するalert.logやトレースファイル、これらの文字コードや言語はNLS_LANGと一切関係なく、データベースキャラクタセットと初期化パラメータNLS_LANGUAGEに依存する。そう考えると、そもそもUTL_FILEが何故DBインスタンスを構成するプロセスのNLS_LANGに依存した作りになっているのか理解に苦しむ。単純にデータベースキャラクタセットと同じ文字コードでファイル出力する、という仕様にすれば良いだけの話である。特にエラーが出る訳でもなく、文字コードの組み合わせによっては事象が顕在化しない場合もあるため、顕在化した際の業務的なインパクトが計り知れない。せめて、設定の矛盾をalert.logに出力するなど、気付くきっかけくらいは考えてくれてもよいのではないか。そう考えると、このUTL_FILEの仕様は極めて不親切であり、どちらかというと、ワークアラウンドが確立されたため修正される見込みのない不具合ではないか、という気すらする。

今回の件を通し、先のUTL_FILEの仕様を読み、それを咀嚼してOracleの環境設計に取り込めるOracle技術者はどれほど存在するだろうか、と考えさせられた。このUTL_FILEの仕様は古く9i時代にさかのぼるらしいが、11gR2からのGIの登場によりより問題として顕在化し易くなったのではないだろうか。世の中的にはあるあるネタで知っている人は多いかもしれないが、少し不安に思った方は自分の身を守るために今一度ご自身の環境をチェックされては如何だろうか。

以上