スポンサーリンク

データベースエンジニアとして5年間実務して、データベーススペシャリストの資格保持者のなかつです。

当記事では、ORA-01840のエラー発生時に解決する方法を紹介します。

確認ポイントや確認用SQLも載せていますので参考にしてください。

 

紹介するポイントをもとに、データやSQLの記述を見直してみましょう!

 

\oracleを使うプロジェクトなら、一冊は持っておくといいかも!/

本については一番最後にもう一度掲載しているので、まずはサクッとエラーを解決しちゃいましょう!

スポンサーリンク

ORA-01840: 入力した値の長さが日付形式に〜のエラー内容

ORA-01840: 入力した値の長さが日付形式に〜のエラー内容

このエラーは、日付型のカラムにデータを入れるときに発生します。

エラーの原因になっているひとつの要因として、oracleの暗黙的変換があります。まずは、暗黙的変換を理解しましょう!

oracleの暗黙的変換とは?

暗黙的変換がわかっている人は、次の章へ飛んでください。

oracleではデータの型に合わせるように自動的に投入データを変換してくれます。

今回のエラーは暗黙的変換が裏目に出ている可能性が高いです。そこで暗黙的変換を少し解説します。

 

先に要点を伝えると「oracle側でデータの型に合わせて、投入データを変換すること」を暗黙的変換と呼びます。

例えば、日付型に対して文字型データを投入する場合に発生します。日付型の定義はインスタンスごとに決められています

その定義にあっていない場合、’2018/06/14’という文字を、勝手に日付型の定義に変換しようとします。これが暗黙的変換です。

 

ちなみに、暗黙的変換を使うとINDEXが効かなくなる等のデメリットがあり、処理速度が著しく落ちます。

できるだけ明示的に変換をかけてあげることが大切です。

 

今回のエラーとの直接的な要因ですが、環境設定で決められている日付型に変換する際にデータ不備があると判断された場合にエラーとなります

スポンサーリンク

ORA-01840: 入力した値の長さが日付形式に〜の解決ポイント

ORA-01840: 入力した値の長さが日付形式に〜の解決ポイント

ORA-01840の解決ポイントを3つ紹介します。

1.DATE型の定義を見直す

DATE型と一言で言っても、’YYYY/MM/DD ‘なのか’DD-MON-YY’なのか定義によって、形が変化しています。

テーブル作成時、もしくはドメイン定義時などに決めていると思うのですが、create文やdatadictionaryを確認してください

定義と投入データがあっているか確認しましょう!

2.投入するデータを明示的に変換する

投入するデータを明示的に変換する

insertでデータを投入する場合、別テーブルからselectするということがあると思います。

別テーブルから抜き出したデータは、投入する側のデータ型(DATE型)とあっているでしょうか?

 

もしあっていなければ、以下を追記して解決しましょう!

TO_DATE(投入するテーブル.カラム名,’YYYY/MM/DD’)

3.NLS_DATE_FORMATを確認

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';

 

 

作業手順としては、以下がオススメです!

作業手順
    1. セッションの環境変数を変更
    2. その形でselectを行う
    3. selectした形のままinsertする

      永続的にNLS_DATE_FORMATを変更したい場合は、spfileを変更しましょう!

      sqlplus> alter system set NLS_DATE_FORMAT = 'YYYY/MM/DD' SCOPE=SPFILE;

       

      ORA-01840: 入力した値の長さが日付形式に〜の解決方法と3つの確認ポイントのまとめ

      ORA-01840: 入力した値の長さが日付形式に〜の解決方法

      今回はORA-01840のエラー解決方法を紹介しました。解決するポイントを抑えて修正、対応してもらえればと思います。

      暗黙的変換や初期の環境設定値など、把握できていると楽かもしれません。プロジェクトによっても異なると思うので、最初に確認しておくことをおすすめします。

       

      \oracleを使うプロジェクトなら、一冊は持っておくといいかも!/

       

      システムエンジニアでも色々な職種があって、さらに上流に行きたいひとや「データベースなんてやりたくない!」っていうひともいるんじゃないかな?

      それでもエラーを解決する力は、どこでも役に立つのでこれからも頑張りましょう!

       

      ところでエンジニアは労働環境がひどいと言われてますが、あなたのところはどうでしょうか?

      僕のところはあまり残業が多くなく、27歳で年収600円以上をもらえる会社でした。(フリーランスになるために辞めたのですが。)

       

      もし今の年収で納得していないなら、まず自分の市場価値を確かめてみてはいかがでしょうか?

      無料で、たった10分で市場価値を図れるので、もし「自分はもっと給料もらっていいはずだ!」と思っている人は市場価値を調査してみてください!

       

      \今だけ無料で市場価値を診断!/

       

      スポンサーリンク

      Twitterでフォローしよう

      おすすめの記事