ORA-01843 无效的月份

现象：

提供给现场PSO的SQL脚本在本地测试绝对没问题，但是到了现场环境就 报ORA-01843

 

SQL如下：

Insert into PMS_TEMPLATE(TPL_ID,TPL_NAME,SRC_TYPE,MODEL_ID,UPTPL_ID,SUIT_ID,TPL_SIZE,TPL_PATH,TPL_NOTE,TPL_TIME) 
values (1201,'三星 GALAXY S4预售专题页',1,null,156,4,0,'/4/ecpsactivity/presale/preSale',null,to_timestamp('12-3月 -13 03.26.33.414000000 下午','DD-MON-RR HH.MI.SS.FF AM'));

  

 

最后查出原因：

现场数据库中NLS_DATE_LANGUAGE是AMRICA，当然不会识别中文的月份格式化了由于更改生产库中的参数会造成不必要的风险，所以修改当前会话的参数。主要SQL语句如下 

--查看日期格式及语言
select * from v$nls_parameters where parameter like '%DATE%';

--更改当前会话参数
alter session set nls_date_language='SIMPLIFIED CHINESE';

 

 附：ORACLE对于 NLS_DATE_LANGUAGE 的阐述：

NLS_DATE_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.

NLS_LANGUAGE specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.