无法一劳永逸更改orcale nls_date_format默认格式DD-MON-RR

问题描述：从Oracle9i开始，日期型数据输入和输出的默认格式为 DD-MON-RR，而在这之前的版本中为DD-MON-YY。

故当未明确指定 转换格式时，会采用oracle的默认格式，如：

               select  to_char(sysdate)  from dual；         执行结果  12-12月-12 。

               RR格式因将日期转换成字符串时，将年份的世纪丢失，故因此将可能带来系统Bug，产生各种隐患。如计算从今天（2013-12-12）开始，36500天后的日期：

               select  to_date(to_char(sysdate+36500))  from dual ，计算结果为 2013/11/18。

               正确的计算结果应是   2113/11/18，即由于RR格式取值规则，而导致年份的世纪为错值。故希望通过更改 oracle默认格式NLS_DATE_FORMAT，将DD-MON-RR更改为有四位年份的 YYYY-MM-DD。不管任何人，通过何种客户端，在没有进行特定设置的情况下，均采用   YYYY-MM-DD默认转换格式。

资料查询：可以从Session, Instance, Database三个级别来对NLS参数进行设置。Session级别的设置覆盖Instance级别的设置，Instance级别的设置覆盖Database级别的设置。相应地，Oracle提供NLS_SESSION_PARAMETERS，NLS_INSTANCE_PARAMETERS，NLS_DATABASE_PARAMETERS这3个视图来查看NLS参数。

A) NLS_DATABASE_PARAMETERS

    此视图来源于sys.props$ 系统表，表示数据库字符集。官网说明： NLS_DATABASE_PARAMETERS lists permanent NLS parameters of the database.。如果INSTANCE和SESSION参数没有设定，那么默认使用这里的参数。

B) NLS_INSTANCE_PARAMETERS
   此视图来源与v$system_parameter（也是视图）,  视图中的参数设定是数据库启动时参照init.ora中的设定而来。 可以通过"ALTER SYSTEM"语句更改。

A) NLS_SESSION_PARAMETERS

  此视图来源于 v$nls_parameter（也是视图），官网说明： v$nls_parameter   contains current values of NLS parameters。 可查看当前session下的字符设置。  该视图 参数可受环境变量、注册表等影响。可通过 "ALTER SESSION"语句更改，但会话结束即失效，无法永久更改。

  

      概括来说，NLS参数设置优先级如下：

      SQL函数指定 >  Alter session >   客户端环境变量、注册表 > 服务器初始化参数(instance级) > 数据库默认值(database级)

  

测试过程（测试库： amicos_old库 ） ：

     未修改前，数据库各视图 NLS_DATE_FORMAT 查询值：

     select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT'     —— DD-MON-RR

      select value from   nls_instance_parameters    where parameter = 'NLS_DATE_FORMAT'     —— 空值

      select value from   nls_session_parameters    where parameter = 'NLS_DATE_FORMAT'       —— DD-MON-RR

1）通过sysdba权限，修改 sys.props$系统表， NLS_DATE_FORMAT 格式为 “ YYYY-MM-DD HH24:MI:SS”。重启数据库， nls_database_parameters 表 查询显示设置已变更。但是PL/SQL执行 select   to_char(sysdate) from dual  ，还是DD-MON-RR格式。说明  session 级比database级优先。

2）在客户端 设定环境变量 NLS_DATE_FORMAT = YYYY-MM-DD。 关闭PL/SQL，重新打开，不管登录任何数据库（即 amicos_old库及其 以外的数据库）， nls_session_parameters 的格式 均变为    YYYY-MM-DD。 说明环境变量生效。PL/SQL端连接所有数据库，全部且永久有效。

3）测试JAVA web环境是否有效。测试结果 即使设定环境变量、注册表， web端  nls_session_parameters  还是为 DD-MON-RR，说明JAVA程序不会读取环境变量、注册表。 

      从JAVA web环境测试反应，可能即使找到相应的设置方法，也不可能对所有用户的机子，进行相关设置。除了web端，此外还有amicos、amicosreport客户端，也均需要设置，才能修改session级的字符级设置。因此，只能从SQL语句编写规范性出发，才能避免此种问题及隐患。

错误写法举例：（目前所想到的，不足请大家补充）

1）使用to_char 将日期转换成字符时，指定格式不能省略

      错误写法：select  to_date(to_char(sysdate+30000)) from dual           执行结果：1996/2/1

      正确写法：select  to_date(to_char(sysdate+30000,'yyyy-mm-dd'), 'yyyy-mm-dd')  from dual   执行结果：2096/2/1

 

2）date类型隐性转换时，如date类型与字符串连接，date类型会先编译成字符串，采用默认转换格式DD-MON-RR, 年份世纪丢失。SQL执行时，将编译后的默认格式日期字符串，转换回date，导致结果错误：

      错误写法：mydate date := sysdate + 300000;

                           v_sql = ' update taskmsn set next_due_date ='' ' ||  mydate || ‘''  where taskid =  ' || p_taskid  ;

                          execute immediate v_sql;

 

                          v_sql 语句在执行前，已编译为字符串： update taskmsn set next_due_date ='01-2月 -96' where taskid..

世纪已丢失。

 

      正确写法：如上这种 date类型会先被编译成字符串，再执行 的模式，若无法直接执行SQL语句，则需在 sql语句编译前，更改session的默认nls_date_parameter格式，只有是四位年份的格式即可。

                          mydate date := sysdate + 300000;

                          EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-YYYY HH24:MI:SS'''; 

                           v_sql = ' update taskmsn set next_due_date ='' ' || mydate  || ‘'' where taskid = ' || p_taskid ;

                           execute immediate v_sql;