to_dateが文字列に変換された場合のORA-01843 not a valid month問題解析

詳細
(注、本明細書の3度の原稿)OracleSQLまたはPL/SQLプログラムの開発中に、文字列タイプをDATEタイプに変換する必要があることがよくあります.to_を使いますdateはこの変換需要を実現する.しかし、この変換でエラーが発生することがあります.ORA-01843:not a valid monthと報告します.このような変換は、開発システムでテストするときに正常であり、本番環境に導入するときにこのようなエラーが発生することがあります.たとえば、文字列タイプの文字列'19-JUN-01'を時間タイプのオブジェクトに変換する必要があります.この文字列は、一般的な英語環境での時間表現「日-月-年」で、「月」は英語で略記されています.だからto_を使うdate(xx,'dd-mon-yy')はそれを変換し、変換フォーマットは'dd-mon-yy'である.ただし、実際の変換操作では、次のように変換に失敗します.

SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;
select  To_Date('19-JUN-01','dd-mon-yy') from dual
ORA-01843: not a valid month

エラーメッセージは、「月」に対応するデータが無効であることを示します.つまり、to_date関数はこのシステムでは「JUN」を認識できない.これは英語の月簡です.中国語の表現に変えれば、「19-10月-01」などです.システムが正しく変換されます.次のようになります.

SQL>  select  To_Date('19-10 -01','dd-mon-yy') from dual;
TO_DATE('19-10 -01','DD-MON-Y
------------------------------
2001-10-19

この問題の本質は、システムが英語の月簡写を認識できず、中国語を認識できることだ.
 
(miki西遊@mikixiyou原文リンク:http://mikixiyou.iteye.com/blog/1735466)Oracleシステムの言語構成は主にV$NLS_に保存されます.PARAMETERSデータ辞書ビューにあります.言語の設定値についてビューをクエリーします.次のようになります.

SQL> select * from v$nls_parameters where parameter like '%DATE%';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE

データベース・システムが言語環境が簡体字中国語であることを確認します.これもデータベース・インスタンス・レベルの構成です.To_を使用する必要がある場合Date('19-JUN-01','dd-mon-yy')は正常に動作し、NLSを修正するだけです.DATE_LANGUAGEの値は、AMERICANに変更すると認識できます.開発者にとって、インスタンスレベルの言語設定パラメータNLS_DATE_LANGUAGEは修正できませんが、セッションレベルを変更できます.データベース・インスタンスに接続しているセッションでパラメータ値をAMERICANに変更すれば、変換できます.

SQL> alter session set nls_date_language='american';
 
Session altered
 
SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;
 
TO_DATE('19-JUN-01','DD-MON-YY
------------------------------
2001-6-19
 
SQL> 

これで使えます.インスタンスレベルのNLS_の場合DATE_LANGUAGEパラメータ値はAMERICANであり,'19-10月-01'をDATEに変換する必要がある場合でも同様の操作が可能である.これは、セッションレベルのパラメータ構成を変更してからto_を使用します.date変換操作は、開発中に面倒な場合があります.ここにはもう一つの方法があります.to_を使用します.dateでもできます.次のようになります.
 

SQL>  select  To_Date('19-10 -01','dd-mon-yy','NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
 
TO_DATE('19-10 -01','DD-MON-Y
------------------------------
2001-10-19
 
 
SQL> select  To_Date('19-JUN-01','dd-mon-yy','NLS_DATE_LANGUAGE = American') from dual;
 
TO_DATE('19-JUN-01','DD-MON-YY
------------------------------
2001-6-19

 
以上から分かるように、to_dateの関数は複雑で強力です.
 
 
データベースに接続されているアプリケーションサーバのオペレーティングシステムの環境変数NLS_を変更する解決策をもう一つ提供します.LANGの配置は、この問題を解決することもできます.
クライアントがwindowsシステムである場合、その環境変数NLS_を修正します.LANG操作は以下の通りです.
 

D:\>set NLS_LANG=american_america.zhs16gbk

D:\>sqlplus htzq/htzq@servdb4_192.168.15.209
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 27 15:47:35 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
------------
27-NOV-12

SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;

TO_DATE('19-
------------
19-JUN-01

ここで、to_について説明します.dateの変換プロセス.(個人的な見解は、必ずしも正しいとは限らない)
to_date関数変換文字列が時間型の場合、クライアントオペレーティングシステム環境変数NLS_を読み込むLANGの構成は、クライアントがこの構成を持っていない場合は、データベース・インスタンスからv$nls_を取得します.parametersのNLS_DATE_LANGUAGEの構成値は、セッションレベルNLSパラメータ構成値を形成する.この値はUGA(おそらくPGAの一部)に保存されている.したがって、alterセッションを使用してパラメータ構成値を変更できます.延長すると、「dd-mon-yy」も省略することができます.この省略されたパラメータはv$nls_を取ります.parametersのNLS_DATE_FORMATパラメータの値.
 
NLS_DATE_LANGUAGE  derived from NLS_LANGUAGE, specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.
 
 

SQL> select * from v$nls_parameters where parameter like '%DATE%';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE
 

SQL> alter session set nls_date_language='american';

Session altered.

SQL> select * from dual where sysdate>TO_DATE('19-JUN-01');

D
-
X

SQL> alter session set nls_date_language='SIMPLIFIED CHINESE';

Session altered.

SQL> select  * FROM DUAL WHERE SYSDATE >To_Date('19-10 -01');

D
-
X

直接to_を使用しますdateは、文字列'19-JUN-01'と'19-10月-01'をdateタイプのデータに変換します.sysdateと直接比較できますさらに、oracleではto_dateは省略できます.
 

SQL> select * from dual where sysdate>'19-JUN-01';

D
-
X

 
Oracleがバックグラウンドで変換されるため、'19-JUN-01'とsysdateを直接使用して比較することもできますが、この方法は推奨されません.