データベースエンジニアとして5年間実務して、データベーススペシャリストの資格保持者のなかつです。
当記事では、ORA-01840のエラー発生時に解決する方法を紹介します。
確認ポイントや確認用SQLも載せていますので参考にしてください。
紹介するポイントをもとに、データやSQLの記述を見直してみましょう！
本については一番最後にもう一度掲載しているので、まずはサクッとエラーを解決しちゃいましょう！
リンクで飛べる目次
ORA-01840: 入力した値の長さが日付形式に〜のエラー内容
このエラーは、日付型のカラムにデータを入れるときに発生します。
エラーの原因になっているひとつの要因として、oracleの暗黙的変換があります。まずは、暗黙的変換を理解しましょう！
oracleの暗黙的変換とは？
暗黙的変換がわかっている人は、次の章へ飛んでください。
oracleではデータの型に合わせるように自動的に投入データを変換してくれます。
今回のエラーは暗黙的変換が裏目に出ている可能性が高いです。そこで暗黙的変換を少し解説します。
先に要点を伝えると「oracle側でデータの型に合わせて、投入データを変換すること」を暗黙的変換と呼びます。
例えば、日付型に対して文字型データを投入する場合に発生します。日付型の定義はインスタンスごとに決められています。
その定義にあっていない場合、’2018/06/14’という文字を、勝手に日付型の定義に変換しようとします。これが暗黙的変換です。
ちなみに、暗黙的変換を使うとINDEXが効かなくなる等のデメリットがあり、処理速度が著しく落ちます。
できるだけ明示的に変換をかけてあげることが大切です。
今回のエラーとの直接的な要因ですが、環境設定で決められている日付型に変換する際にデータ不備があると判断された場合にエラーとなります。
ORA-01840: 入力した値の長さが日付形式に〜の解決ポイント
ORA-01840の解決ポイントを３つ紹介します。
１．DATE型の定義を見直す
DATE型と一言で言っても、’YYYY/MM/DD ‘なのか’DD-MON-YY’なのか定義によって、形が変化しています。
テーブル作成時、もしくはドメイン定義時などに決めていると思うのですが、create文やdatadictionaryを確認してください。
定義と投入データがあっているか確認しましょう！
２．投入するデータを明示的に変換する
insertでデータを投入する場合、別テーブルからselectするということがあると思います。
別テーブルから抜き出したデータは、投入する側のデータ型（DATE型）とあっているでしょうか？
もしあっていなければ、以下を追記して解決しましょう！
TO_DATE(投入するテーブル.カラム名,’YYYY/MM/DD’)３．NLS_DATE_FORMATを確認
環境ごとにNLS_DATE_FORMATが設定されています。環境設定を確認して意図しているデータ型になっているか確認しましょう！
よくあることなのですが、開発環境と本番環境でNLS_DATE_FORMATが異なっていることがあります。「開発環境では流れたのに…」という場合には、環境設定を疑いましょう。
まずは以下のSQLを流して、NLSの関連のパラメータの確認してください。
select * from v$nls_parameters;その後の解決方法は以下のどちらかです。業務に合わせて選択してください。
- 投入データを加工する
- データベースのカラム定義を変更する
ちなみにSQLplusなどでDBに繋いでいる場合、そのセッションの間だけDATE型のパラメータを変更することも可能です。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
作業手順としては、以下がオススメです！
- セッションの環境変数を変更
- その形でselectを行う
- selectした形のままinsertする
永続的にNLS_DATE_FORMATを変更したい場合は、spfileを変更しましょう！
sqlplus> alter system set NLS_DATE_FORMAT = 'YYYY/MM/DD' SCOPE=SPFILE;
ORA-01840: 入力した値の長さが日付形式に〜の解決方法と３つの確認ポイントのまとめ
今回はORA-01840のエラー解決方法を紹介しました。解決するポイントを抑えて修正、対応してもらえればと思います。
暗黙的変換や初期の環境設定値など、把握できていると楽かもしれません。プロジェクトによっても異なると思うので、最初に確認しておくことをおすすめします。
システムエンジニアでも色々な職種があって、さらに上流に行きたいひとや「データベースなんてやりたくない！」っていうひともいるんじゃないかな？
それでもエラーを解決する力は、どこでも役に立つのでこれからも頑張りましょう！
ところでエンジニアは労働環境がひどいと言われてますが、あなたのところはどうでしょうか？
僕のところはあまり残業が多くなく、27歳で年収600円以上をもらえる会社でした。（フリーランスになるために辞めたのですが。）
もし今の年収で納得していないなら、まず自分の市場価値を確かめてみてはいかがでしょうか？
無料で、たった10分で市場価値を図れるので、もし「自分はもっと給料もらっていいはずだ！」と思っている人は市場価値を調査してみてください！
＼今だけ無料で市場価値を診断！／
ITエンジニアにおすすめの記事
＞＞レバテックフリーランスの評判と口コミを実際に使った現役フリーランスSEが語る
