Java＋Oracle環境で開発していて、JPQLでは対応できない処理があったため、ネイティブクエリ（直接SQLを実行する機能）を使ってSQLを実行させる処理がありました。
開発している環境（社内サーバ）では正常に動いていたのに、本番で動かす環境（サーバ実機）で動かしたら、なぜかORA-01861エラーが発生してしまうという状況に。
同じ過ちを犯さないためにも、その時の対策方法をメモメモ。
比較時の型がマッチしていない
エラーメッセージによると、このエラーが出た原因は、比較しようとしている型がマッチしていない、とのこと。
開発機と本番機は構成がほぼ一緒で、載せているシステム環境も一緒。実行したSQLも全く一緒なのに、なぜか本番環境だけがこんなエラー。試しに、SQL Plusで全く同じSQLを叩いてみても、エラーなく結果が表示される。
SQLを書く上でよくやるのが、文字列なのにクォートで囲っていなかったり、数値なのにクォートで囲ってしまったり。エラーにならず正常に処理が進んでしまうこともありますが、内部的には暗黙の型変換がされていて、のちのちバグを生む原因になることも多かったり。
SQL構文にミスマッチ
ということで、一度原点に戻り、DBのテーブル構成とSQL構文を並べてにらめっこしてみたところ、なんとなく引っかかったのが以下の部分。
where update_date < '2008-04-01'
たぶん、ほとんどの人が特に気にすることなくこういう記述をしていると思う。自分もこう書くことが多い。
でも厳密に考えると、update_dateはdate型で持っているので、'2008-04-01'という書き方だとstring型になってしまうから、date型とstring型の比較はできないよ！って言われているような気がする。なんだかそんな雰囲気。
そこで、以下のように修正してみる。
where update_date < TO_DATE('2008-04-01','yyyy-mm-dd')
string型をdate型に変換させてあげるため、oracleのTO_DATE関数を使う。第一引数にstring型の日付を入れ、第二引数でフォーマットを指定してあげる。こうすることで、フォーマットの書式にしたがってdate型に変換してもらえます。
さっそく実行してみると、エラーも消えて問題なく通るようになりました。
Oracleの暗黙変換に注意
ORA-01861エラーは日付の暗黙変換で起こることが多いようです。え、ちゃんと日付になってるじゃん！と言いたくなりますが、そもそもデフォルトのフォーマット指定でこの日付形式が設定されていなければ、エラーになってしまうわけです。デフォルトフォーマットというのは、以下の3つ。
NLS_TIMESTAMP_FORMAT
NLS_DATE_FORMAT
NLS_TIME_FORMAT
開発機のフォーマットを確認してみました。
NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF'
NLS_DATE_FORMAT='YYYY-MM-DD'
NLS_TIME_FORMAT=='HH24:MI:SSXFF'
本番機のフォーマットを確認してみました。
NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF'
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
NLS_TIME_FORMAT=='HH24:MI:SSXFF'
おお、まさにNLS_DATE_FORMATの違いでエラーになっていることがわかります。ということは、TO_DATE関数を使わなくても、
where update_date < '2008-04-01 00:00:00'
でもいいわけですね。
JPQLは設定の差を埋めてくれる
上記のような処理も、JPQLで記述していればどちらもエラーにならずに処理が進むこともあります。全てとは言い切れませんが、ほとんどの場合で動きます。JPQLではこの辺の型変換を上手にやってくれるのです。
でも、ネイティブクエリでは全て自分で面倒を見てあげないといけないので、比較するときの型はきちんと合わせるべきなのです。
コメント