Oracle Objectでの日付書式

2012/05/19 11:13

会社での開発作業の話。

今開発しているシステムは、旧システム老朽化の対策で新サーバーへの移行を行っている。

その旧システムはOracle7で、新システムはOracle11g

その移行作業を行っていたのだが、クライアントではODBCではなくOracle Objectを使用してデータベースアクセスを行っていた。

恐らくODBCではパフォーマンス的に悪いために専用APIであるOracle Objectを使用していたのかと思われる。(既に旧システム開発時の担当がいないので不明だが)

で、今回はこのクライアントで使用しているOracle Objectで問題が出た。

クライアントはVisual Basic6製で、すんなりOracle11g用にビルドすることはできた。

だが、起動してみるとエラーが発生して正常に処理できない。

調べて見ると1部Oracle Objectのメソッド名が変更されていることがわかった。

DbCreateDatabaseというメソッドがその問題のメソッド。

調べて見ると、DbCreateDatabaseはOracle8 R8.0.5までのメソッドでそれ以降は削除されたらしい。

旧システムでは更にこのOracle Objectでデータベースアクセスする部分が、ActiveXコンポーネントとしてDLL化されていたため、更に分かり難かった。

しかもコンパイルはこれでも正常に終わる。

そして実行時にバインディングでエラーとなり、初めて発覚するということ。

なんて下位互換になってないのか...インターフェースは変更しないのが原則じゃないのだろうか。

で、本題はここから。

クライアントでのエラーは解決し、データベースのマスタ検索は問題ないことが確認できた。

しかし、マスタへの追加や更新を行おうとすると「ORA-01861 リテラルがフォーマット文字列と一致しません」のエラーが発生。

調べてみると結構このエラーは有名みたいで、ほとんどの原因は日付の書式がプログラムで指定している形式とデータベース側で合致していないことが原因とあった。

確かにこのマスタにはDATE型の項目があり、INSERTする際も'2012.05.18 15:00:10'のように指定していた。

そして通常このエラーに対しては、日付を指定する際にTO_DATEを使用すればいいとあり、確かにTO_DATE('2012.05.18 15:00:10','YYYY.MM.DD HH24:MI:SS')と指定すれば、問題なくINSERTやUPDATEできることは確認した。

しかし、この方法では全部で100本近くあるプログラムの日付を使用している項目は全て修正する必要があり、工数的に厳しいものがある。

しかも、Oracle7では問題なくこれでも動作していたにも関わらず、Oracle11gでは駄目というのはまたもや互換性の問題じゃないのかと。

いや、環境設定に問題があるのか?

もし、環境設定のみで解決できればプログラムは修正する必要がなくなる。

そういうわけで、色々と調べてみた。

まず見つかったのは、グローバリゼーション・サポート・ガイド3.グローバリゼーション・サポート環境の設定日付および時間を指定するパラメータに書いてあるNLS_DATE_FORMATだ。

これはその名前の通り、DATE型の書式を設定する。

そしてこのNLS_DATE_FORMATは、データベースのパラメータファイルや環境変数に指定することができる。

通常は環境変数に指定するらしい。

Windowsであれば、マイコンピュータプロパティ詳細設定環境変数でユーザー毎の環境変数かシステムの環境変数のどちらかに設定する。

例えば変数名は「NLS_DATE_FORMAT」、変数値は「YYYY.MM.DD HH24:MI:SS」という風に指定する。

ほとんどの例では日付書式を日の部分までしか指定していないが、DATE型は時間も含めることができるため、書式にも時間まで含めて指定することはできる。

対処はわかったので実際にやってみたが、これがうまくいかない。

更に調べてみるとOracleのフォーラムに「NLS_DATE_FORMATについて」というスレッドがあり、その中に

オラクル社の現在のリリースでの仕様では、INIT.ORAファイルの
指定より、各クライアントに、NLS_LANG = JAPANESE_JAPAN.xxxxxxx
が設定してあると、必ず日付書式がYY-MM-DDになってしまうため
NLS_DATE_FORMATをレジストリーに設定するか、プログラムの先頭で、
ALTER SESSION文を追加しなくてはなりません。”(原文)

とありました。

古い文献かららしいですが今でも修正されたという報告がないので11gでも変わらないのではないのかということです。

つまり環境変数では駄目でレジストリに登録する必要があるということ。

早速、レジストリ「\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME」(KEY_HOME_NAMEはOracleインストール時のホーム名)に「NLS_DATE_FORMAT」を追加します。

ちなみにこのレジストリパラメータはOracleのマニュアル「Oracle Database プラットフォーム・ガイド」の15 パラメータおよびレジストリの構成に書いてあります。

ということで再度レジストリに登録してみたが、それでも解決せず相変わらずORA-01861エラーが発生する。

そこで考えてみたのだが、今回はODBCではなくOracle Object経由。

そのため、これらの設定はOracle Objectでは無視されているのではないのかと予想してみた。

そして、次はOracle Objectと日付書式で調べてみた結果、更に新しい事実が判明した。

その記述は「Oracle Provider for OLE DB開発者ガイド2 OraOLEDBの機能に書いてある1文。

Oracle Databaseへの接続する際の接続文字列の1つに「UseSessionFormat」というのがあり、これが

セッションの間、NLSセッションのデフォルトの書式を使用するか、OraOLEDBでこれらの書式の一部を上書きするかを指定します。有効値は0(FALSE)と1(TRUE)です。デフォルト値はFALSEで、OraOLEDBはNLSセッションのデフォルトの書式の一部を上書きできます。値がTRUEの場合、OraOLEDBはNLSセッションのデフォルトの書式を使用します。”(原文)

と書かれていた。

更にその下の方の日付書式に

Oracleセッションの日付書式は、ALTER SESSION SET NLS_DATE_FORMATコマンドを使用しても設定できません。(中略)Oracle Provider for OLE DBでは、ConnectionプロパティのUseSessionFormatがデフォルト値のFALSEに設定されている場合、セッションのNLS_DATE_FORMATが、プロバイダによって'YYYY-MM-DD HH24:MI:SS'に固定されています。日付を文字列としてOracle Databaseに渡す場合、'YYYY-MM-DD HH24:MI:SS'の形式にする必要があります。UseSessionFormatがTRUEに設定されている場合、NLS_DATE_FORMATはOracle Provider for OLE DBによって固定されず、デフォルト・セッションのNLS_DATE_FORMATが使用されます。”(原文)

とあり、どうやらプロバイダで固定されるのでUseSessionFormatプロパティを変更しろということだ。

つまり、Oracle ObjectではなくOracle Provider for OLEの問題であったということで、別にOracle Objectが悪いわけではなかった。

さて、実はこの解答はさきほど見つけたばかりでまだ検証ができていない。

月曜日に出社したら早速試して確認することにしよう。

確認後はこの記事も修正しようと思う。

→検証できました。

但し、接続文字列に追加する場合は結局プログラム修正が必要なので、レジストリで対応できないかと検証しました。

結論から言うと、対応できました。

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDBUseSessionForamtを追加して値に「1」を指定し、\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME配下にNLS_DATE_FORMATを追加して値に「YYYY/MM/DD HH24:MI:SS」を指定します。

この2つを追加することで、デフォルトの日付書式を変えることができました。

プログラムも未修正のままでそのままINSERT、UPDATEでき、問題は解決しました。

こんなに手こずるとは思わなかった...