PL/SQLプロシージャで ORA-01861 で詰んだ件

今まで動いていたPL/SQLプロシージャが
ある日突然動かなくなりました。

ORA-01861 : literal does not match format string (リテラルが書式文字列と一致しません。)

３つの問題が同時に起きていたのを切り分けできず
相当な時間を浪費してしまったケースをご紹介。

答えから先に言うと、

• 日付のformatが異なっていると発生するOracleエラー
• nls_parametersのdate_langが勝手に書き換えられていた
• プロシージャの入力がDATE型で、入力に至る前に暗黙的なVARCHAR→DATEをやってた

という３つの問題が同時発生していました。

いろんな書き方をするよねみんな

日付を文字列で表現するときは、
人によっていろんな書き方をします。

例えば「2017年1月12日」なら

'17-01-12' --'RR-MM-DD'

だの

'12-JAN-17'　--'DD-MMM-RR'

だの

'20170112'　--'YYYYMMDD'

だの

人によってかなり好き勝手に表現します。

これの指定が間違っている時に
ORA-01861
が発生します。

フォーマットを指定して変換する例

TO_DATE('17-01-12','RR-MM-DD')

第二引数を省略した場合は
環境変数的なNLSパラメータに依存します。

NLSパラメータを確認するSQL

 select * from v$nls_parameters;

プロシージャの処理の前で暗黙的な変換してた件！

create or replace PROCEDURE hoge(
  inputHoge IN OUT VARCHAR2
  inputDate IN OUT DATE
  )

で、ここが一番悩んだのですが、

ORA-エラーを吐いていたのは
プロシージャの入力がDATE型だったので
そこで暗黙的変換をしていたのに気が付かなかったのです。

以下のように変更して回避しました。

create or replace PROCEDURE hoge(
  inputHoge IN OUT VARCHAR2
  inputDateChr IN OUT VARCHAR2
  )

一度文字列型のまま受け取って、
プロシージャの下の方で明示的に変換する処理を追加。

この投稿が誰かのお役に立てば幸いです。