Oracleの日付変換トラブル

関わった案件で発生したOracle(暗黙)日付変換に関するトラブルを紹介します。

以下のような記載があったPL/SQLについて、ある環境では正常終了するのに別の環境ではエラーが発生するという不可解な事象が発生しました。

hensu := to_date(hiduke,'YYYY/MM/DD');

hiduke変数の値を日付型に[YYYY/MM/DD]フォーマットで変換して、hensu変数に格納するというありふれた代入式です。 SQLでも、where条件に WHERE A.HIDUKE = TO_DATE(B.SYORI_BI,'YYYY/MM/DD')なんて書いたりしますよね。

環境Aでは、この処理が通りますが、環境Bでは

 「ORA-01830: 日付書式の変換で不要なデータが含まれています」

というエラーが発生していました。

日付書式なので、[YYYY/MM/DD]と変数hidukeがアンマッチなのでしょうか。しかし、環境Aと環境Bでは同じデータを利用しているので内容は全く同じはず…。

エラーの詳細を調査すると上記の代入式で登場する2つの変数についてデータ型が妙な事になっていました。なんと、hensuとhidukeの両方ともDATE型(日付型)だったのです。つまり、変換しなくても良いものを変換していました。

EclipseやVisualStudioなどの高機能IDEではこのような変換はソースレベルで解析して警告やエラーが出そうな気がしますが…PL/SQLやSQLでは文法的には適切と判断され、内部で変換して処理しようとしてくれます。つまり、暗黙的に何らかの型変換（キャスト）が発生します。

今回の場合、以下のように読み替えられたものと推測されます。

hensu := to_date(hiduke,'YYYY/MM/DD');
↓
hensu := to_date(to_char(hiduke),'YYYY/MM/DD');

to_date関数の第一引数(文字列型)に合うようにhiduke変数をto_char関数で文字列に変換されたと想定されます。仮説が正しければ、環境Aと環境Bでto_char(hiduke)の結果が異なるはずです。

実際に試してみました。（DATE型変数をSYSDATEで代用）

環境A

　select to_char(sysdate) from dual;
　→文字列：15-06-08

環境B：

　select to_char(sysdate) from dual;
　→文字列：2015/06/08 12:00:00

環境によって大きな差異がでました。この結果を受けて両環境で文字列→日付変換を２パターン試してみます。

試験1:

　select to_date('2015/01/01 10:00:00','YYYY/MM/DD') from dual;

試験2:

　select to_date('15-01-01','YYYY/MM/DD') from dual;

環境A:

　試験1:ORA-01830エラー発生
　試験2:表示される

環境B:

　試験1:ORA-01830エラー発生
　試験2:表示される

いずれも、同じような結果となりました。

環境Aでも日付書式と文字列が不適切だと同じエラーになるようですね、つまり推測通り試験1に似た暗黙変換が”環境Bのみ”で発生しているようです。

調査をすすめると、環境Bには日付書式に関する環境変数「NLS_DATE_FORMAT」が(システムレベルで）あらかじめ設定されていたことが分かりました。また、環境Aにはこのような環境変数が存在しないことも分かりました。

つまり、

　環境A：set NLS_DATE_FORMAT=
　環境B：set NLS_DATE_FORMAT=yyyy/mm/dd hh24:mi:ss

の違いがあったということです。

この環境変数により、環境AはRR-MM-DD、環境BではYYYY/MM/DD HH24:MI:SSが暗黙的に日付型から文字列型に変換される際のフォーマットとして利用されていたわけです。（環境Aの書式はデフォルト値のようなもの(※)）※Oracleのデフォルト値はとてもややこしいので、詳細は割愛。

そして、各々でYYYY/MM/DDフォーマットで変換を試みたところ、RR-MM-DDは通り、YYYY/MM/DD HH24:MI:SSが拒否されるという仕様であったため今回のエラーが発生していました。

個人的にはRR-MM-DDがYYYY/MM/DDで変換できてしまうOracleはやり過ぎだと思いますが、きっとこの仕様を変えると世の中にある数万から数十万のシステムに影響がありそうなので、ずっと直せないでしょうね…。

さて、今回のトラブルについては、以下のように修正することで対応しました。

hensu := to_date(hiduke,'YYYY/MM/DD');
↓
hensu := hiduke;

同じ日付型ですので、これで問題ないはずです。

c:\Users\じぶん