Oracle Database のTO_DATEファンクションでORA-01861が発生する

事象

Oracle Database にJDBC経由で接続し、TO_DATEファンクションを日付フォーマット指定無しに実行すると、環境によってORA-01861が発生する場合がある。
例えば、Windows上では問題ないのに、Linux上で同じJavaプログラムを動かすとエラーになることがある。
エラーとなるSQLの例とエラーメッセージ：
select TO_DATE('2014-11-01') from DUAL
java.sql.SQLDataException: ORA-01861: literal does not match format string

解決策

TO_DATEファンクションを日付フォーマット指定して実行する。
select TO_DATE('2014-11-01','YYYY-MM-DD') from DUAL

Oracle Database 12cR1のマニュアルでも推奨されている。

注意:
次の項の例に示すとおり、TO_DATEでは、常に書式マスク(fmt)を指定することをお薦めします。書式マスクを指定しないと、このファンクションは、charがNLS_TERRITORYまたはNLS_DATE_FORMATパラメータで指定されたものと同じ書式を使用している場合にのみ有効になります。さらに、依存性を回避するために明示的な書式マスクが指定されていない場合は、データベース間でファンクションが不安定になることがあります。

TO_CLOB

原因

TO_DATEファンクションの日付フォーマットを指定しない場合、NLS_TERRITORY初期化パラメータもしくはNLS_DATE_FORMAT初期化パラメータで決まる。

デフォルトの日付フォーマットは、NLS_TERRITORY初期化パラメータによって暗黙的に決まります。NLS_DATE_FORMATパラメータを使用して明示的に設定することもできます。

TO_CLOB

NLS_TERRITORY初期化パラメータ及びNLS_DATE_FORMAT初期化パラメータの初期値はJDBCドライバが決定している。

この初期化パラメータNLS_TERRITORYの値は、このパラメータのセッション値の初期化に使用され、SQL問合せの処理で参照される実際の値です。クライアントがOracle JDBCドライバを使用しているか、クライアントがOCIベースでNLS_LANGのクライアント設定(環境変数)が定義されている場合、この初期値はクライアント側の値でオーバーライドされます。そのため、初期化パラメータ値は、通常、無視されます。

http://docs.oracle.com/cd/E57425_01/121/REFRN/refrn10128.htm#CHDBEFJC

この初期化パラメータNLS_DATE_FORMATの値は、このパラメータのセッション値の初期化に使用され、SQL問合せの処理で参照される実際の値です。クライアントがOracle JDBCドライバを使用しているか、クライアントがOCIベースでNLS_LANGのクライアント設定(環境変数)が定義されている場合、この初期値はクライアント側の値でオーバーライドされます。そのため、初期化パラメータ値は、通常、無視されます。

http://docs.oracle.com/cd/E57425_01/121/REFRN/refrn10119.htm#i1129826

クライアントがJDBCドライバを利用していると、NLS_TERRITORY初期化パラメータ及びNLS_DATE_FORMAT初期化パラメータはクライアント側のロケールに応じて設定される。

言語と地域
Thin ドライバによって、JVM user.language プロパティの Java ロケールから言語設定と地域設定（NLS_LANGUAGE と NLS_TERRITORY）が取得されます。日付書式（NLS_DATE_FORMAT）は地域設定に従って設定されます。

http://otndnld.oracle.co.jp/document/oracle9i/920/generic/java/J06299-01.pdf

※古い資料だが、挙動自体は変わっていないものと思われる。

手元の環境で検証したところ、JDBCドライバによってNLS_DATE_FORMAT初期化パラメータは以下のように設定されていた。

OS	環境	NLS_DATE_FORMAT初期化パラメータ
Windows	日本語	RR-MM-DD
Linux	LANG=ja_JP.UTF-8	RR-MM-DD
Linux	LANG=en_US.UTF-8	DD-MON-RR

この結果より、ロケールの違いがNLS_DATE_FORMAT初期化パラメータの設定値の違いとなり、TO_DATEファンクションのエラーにつながったと考えられる。

検証環境

Windows（クライアント）

Windows 8.1（日本語環境）
JDK 1.8.0_20
ojdbc7.jar

Linux（Oracleサーバ兼クライアント）

Oracle Linux 6 Update 5 for x86_64 (64bit)
Oracle Database 12c Release 1 (Linux x86-64)
JDK 1.8.0_20
ojdbc7.jar

検証コード

注意：このコードは動作検証のみを目的としています。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class OracleToDate {
    public static void main(String args[]) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@[host]:[port]:[sid]", "[id]", "[password]");
            Statement stmt = conn.createStatement();

            ResultSet rsFormat = stmt.executeQuery("select VALUE from v$nls_parameters where parameter='NLS_DATE_FORMAT'");
            while ( rsFormat.next() ) {
              System.out.println("NLS_DATE_FORMAT:" + rsFormat.getString(1));
            }
            rsFormat.close();

            ResultSet rsDateFormat = stmt.executeQuery("select TO_DATE('2014-11-01','YYYY-MM-DD') from DUAL");
            while ( rsDateFormat.next() ) {
              System.out.println("NLS_DATE_FORMAT:" + rsDateFormat.getString(1));
            }
            rsDateFormat.close();

            ResultSet rsDate = stmt.executeQuery("select TO_DATE('2014-11-01') from DUAL");
            while ( rsDate.next() ) {
              System.out.println("NLS_DATE_FORMAT:" + rsDate.getString(1));
            }
            rsDate.close();
           
            stmt.close();
            conn.close();
           
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}