新規に質問を投稿する
Oracle：Date型への時刻保存ができない
2020-09-01 15:46
Oracleの Date型カラムに対し、
アクション「テーブル XX のデータを更新する」で「=NOW()」などを保存すると
【ORA-01861: リテラルが書式文字列と一致しません】エラーが発生します。
年月日＋時分秒の数字６つの組み合わせが駄目みたいで、数字３つだけなら
下記のパターンのように保存されます。
「="2020/09/01"」→ '2020/09/01'
「="01:02:03"」 → '2001/02/03'
「=DATE(1,2,3)」→ '2001/02/03'
「=TIME(4,5,6)」→ '2004/05/06'
一方でアクション「テーブル XX から1件取得する」で、Date型カラムから
時刻付きの値を拾ってくると、時刻付きのまま正常に表示されます。
また Oracleの Timestamp型が相手だと、日付＆時刻の組み合わせでも正常に保存されます。
だからと言って、テーブル構造をCELFの都合に合わせて変更などできませんが。
Oracleの Date型は日付＆時刻ですが、MySQLの Date型は時刻を含みませんので、
CELFによる保存時、相手がOracleでもMySQL向けの加工処理をしている？と予想します。
CELFからの書き込み用テーブルを作って、トリガー経由で値をコピーしてやるぐらいしか
対処法が見つからなくて困っております・・・どうかお力添えください。
回答を投稿するにはログインが必要です。
fujiimaさん
ご返信ありがとうございます。
>クライアント接続時（ODBC）は、環境変数 NLS_DATE_FORMAT で対応可能。
>サーバー接続時（JDBC）は NLS_DATE_FORMAT での対応はできない。
オンプレミス版でしたか、、、直接の解決には至らず残念です。
今後、弊社でもオンプレミス版を扱う可能性はある為、
情報の共有ありがとうございました。
確かに他の手段がなさそうなので、仕方ないけれど、
「書き込み用テーブルを作って、トリガー経由で値をコピー」ってなんか悲しいですね。
>CELFのSQL実行アクションが、SELECT文以外も実行できるようになれば
>次のコマンドで解決できるかもしれないのですけど！
>「ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';」
同じことを思って実際に試したりしたんですが、やっぱりSELECT限定なんですよね。
SELECT文以外も実行出来ればよいですよね。
ご返信ありがとうございます。
>クライアント接続時（ODBC）は、環境変数 NLS_DATE_FORMAT で対応可能。
>サーバー接続時（JDBC）は NLS_DATE_FORMAT での対応はできない。
オンプレミス版でしたか、、、直接の解決には至らず残念です。
今後、弊社でもオンプレミス版を扱う可能性はある為、
情報の共有ありがとうございました。
確かに他の手段がなさそうなので、仕方ないけれど、
「書き込み用テーブルを作って、トリガー経由で値をコピー」ってなんか悲しいですね。
>CELFのSQL実行アクションが、SELECT文以外も実行できるようになれば
>次のコマンドで解決できるかもしれないのですけど！
>「ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';」
同じことを思って実際に試したりしたんですが、やっぱりSELECT限定なんですよね。
SELECT文以外も実行出来ればよいですよね。
Riverさん
わざわざ検証をしていただきありがとうございます。
非常に参考になります。
【クライアント接続時】
こちらではまず V$NLS_PARAMETERS の値を CELF で表示させてみました。
何もしないと NLS_DATE_FORMAT の値は "RR-MM-DD" でした。
次に、私の端末の環境変数に NLS_DATE_FORMAT を追加し、curlも再起動しました。
コマンド：「SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS」
結果、ODBC接続している場合には、設定した値 "YYYY-MM-DD HH24:MI:SS" が
V$NLS_PARAMETERS にて無事に取得できました。ありがとうございます。
【サーバー接続時】
ところで、弊社ではオンプレミスで導入しているため、ユーザー向けには
DB接続は「サーバー接続」を使用しています。クライアント接続ではなく。
そこで期待を込めてCELFサーバにも、同環境変数を入れてみましたが、
残念ながら効果はありませんでした。
クライアント接続はODBCドライバ経由ですが、
サーバー接続はCELFサーバ側での、JDBCドライバ経由です。
調べてみるとどうも、JDBCはNLS系環境変数は無視するようです。代わりに
ロケールに合わせて自動設定した結果、日本なら "RR-MM-DD" になるようです。
https://docs.oracle.com/cd/E11882_01/java.112/e16548/global.htm#JJDBC28644
"The JDBC driver does not check NLS environment."
https://docs.oracle.com/cd/E96517_01/xeinl/setting-client-connection-language-preferences.html
"Oracle Databaseへの接続にOracle JDBCを使用するJavaアプリケーションでは、NLS_LANGを使用しません。"
"Java VMのデフォルトのロケールをOracle Databaseのlanguageとterritoryの設定にマップします。"
https://qiita.com/neko_the_shadow/items/baca14a933ddc9d3d562
https://docs.oracle.com/cd/E16338_01/server.112/b56307/ch3globenv.htm
【結論】
クライアント接続時（ODBC）は、環境変数 NLS_DATE_FORMAT で対応可能。
サーバー接続時（JDBC）は NLS_DATE_FORMAT での対応はできない。
CELFサーバはJDBCの仕様に従っているだけなのでイノセント。
というわけで、サーバー接続時は今のところ最初に検討した
「書き込み用テーブルを作って、トリガー経由で値をコピー」
で対応するしかなさそうだなという結論です。
CELFのSQL実行アクションが、SELECT文以外も実行できるようになれば
次のコマンドで解決できるかもしれないのですけど！
「ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';」
ありがとうございました。
わざわざ検証をしていただきありがとうございます。
非常に参考になります。
【クライアント接続時】
こちらではまず V$NLS_PARAMETERS の値を CELF で表示させてみました。
何もしないと NLS_DATE_FORMAT の値は "RR-MM-DD" でした。
次に、私の端末の環境変数に NLS_DATE_FORMAT を追加し、curlも再起動しました。
コマンド：「SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS」
結果、ODBC接続している場合には、設定した値 "YYYY-MM-DD HH24:MI:SS" が
V$NLS_PARAMETERS にて無事に取得できました。ありがとうございます。
【サーバー接続時】
ところで、弊社ではオンプレミスで導入しているため、ユーザー向けには
DB接続は「サーバー接続」を使用しています。クライアント接続ではなく。
そこで期待を込めてCELFサーバにも、同環境変数を入れてみましたが、
残念ながら効果はありませんでした。
クライアント接続はODBCドライバ経由ですが、
サーバー接続はCELFサーバ側での、JDBCドライバ経由です。
調べてみるとどうも、JDBCはNLS系環境変数は無視するようです。代わりに
ロケールに合わせて自動設定した結果、日本なら "RR-MM-DD" になるようです。
https://docs.oracle.com/cd/E11882_01/java.112/e16548/global.htm#JJDBC28644
"The JDBC driver does not check NLS environment."
https://docs.oracle.com/cd/E96517_01/xeinl/setting-client-connection-language-preferences.html
"Oracle Databaseへの接続にOracle JDBCを使用するJavaアプリケーションでは、NLS_LANGを使用しません。"
"Java VMのデフォルトのロケールをOracle Databaseのlanguageとterritoryの設定にマップします。"
https://qiita.com/neko_the_shadow/items/baca14a933ddc9d3d562
https://docs.oracle.com/cd/E16338_01/server.112/b56307/ch3globenv.htm
【結論】
クライアント接続時（ODBC）は、環境変数 NLS_DATE_FORMAT で対応可能。
サーバー接続時（JDBC）は NLS_DATE_FORMAT での対応はできない。
CELFサーバはJDBCの仕様に従っているだけなのでイノセント。
というわけで、サーバー接続時は今のところ最初に検討した
「書き込み用テーブルを作って、トリガー経由で値をコピー」
で対応するしかなさそうだなという結論です。
CELFのSQL実行アクションが、SELECT文以外も実行できるようになれば
次のコマンドで解決できるかもしれないのですけど！
「ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';」
ありがとうございました。
ベストアンサー
fujiimaさん
ローカルPC(Windows10)にOracle18cExpressを
インストールした環境で試したことを共有します。
以下の環境変数を登録する前は、
同じく【ORA-01861: リテラルが書式文字列と一致しません】が発生しましたが、
PCの環境変数に以下の変数を追加した場合、「=NOW()」のセルでも更新できました。
変数名:NLS_DATE_FORMAT
変数値:YYYY-MM-DD HH24:MI:SS
＜注意＞
ただし、ご利用の端末から他のシステムのOracleへ接続する場合も
上記内容で接続されますので、影響がでるかもしれません。
変更される際はご確認のうえ、ご自身の責任範囲でご変更頂ければと思います。
ご参考になれば幸いです。
ローカルPC(Windows10)にOracle18cExpressを
インストールした環境で試したことを共有します。
以下の環境変数を登録する前は、
同じく【ORA-01861: リテラルが書式文字列と一致しません】が発生しましたが、
PCの環境変数に以下の変数を追加した場合、「=NOW()」のセルでも更新できました。
変数名:NLS_DATE_FORMAT
変数値:YYYY-MM-DD HH24:MI:SS
＜注意＞
ただし、ご利用の端末から他のシステムのOracleへ接続する場合も
上記内容で接続されますので、影響がでるかもしれません。
変更される際はご確認のうえ、ご自身の責任範囲でご変更頂ければと思います。
ご参考になれば幸いです。
Q&A一覧へ