[20240814]oracle 21c NLS_DATE_FORMAT设置问题(整理版本1).txt

[20240814]oracle 21c NLS_DATE_FORMAT设置问题(整理版本1).txt

--//朋友遇到的问题,请求远程协助解决问题:
--//执行sqlplus出现如下错误:
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Aug 10 11:38:06 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected.
~~~~~~~~~
SQL> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
--//我开始怀疑对方系统是否是安装防水墙或者防火墙之类导致的问题,我以前在生产系统也遇到类似问题,出现Connected.实际上正常
--//登录不会出现该提示。
--//幸好我即时发现NLS参数设置有单引号(注windows机器连接linux服务器),正好对方前几十分钟做了增加NLS*环境变量的操作,建议
--//他取消看看,问题马上解决,在windows下设置NLS环境变量不需要使用单双引号。
--//也提醒遇到问题要冷静,想想自己做了什么改动,这样能很快定位问题.

--//正好没事,在自己的测试环境重复演示遇到的问题,结果遇到自己一些问题.

1.环境:
SYS@192.168.56.101:1521/book> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立测试环境需要文件:
D:\tmp\study> cat nls.bat
rem @ echo off
set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
echo select sysdate,systimestamp,localtimestamp from dual ; | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
--//注:使用单引号,这样设置无法登录.
--//后面的内容为了避免重复,我直接贴出nls.bat的执行结果,不再贴出nls.bat的内容.

3.测试(单引号):
d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//这里注意一个细节,前面使用sys登录执行,报错ORA-01012: not logged on.而这里使用system用户登录,报错出现ORA-02248:
--//invalid option for ALTER SESSION,至少给一个提示可能知道可能是设置环境变量错误.

d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l sys/bookbook@192.168.56.101:1521/book as sysdba
select sysdate,systimestamp,localtimestamp from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
--//错误提示是ORA-01012: not logged on。
--//后面的测试使用system用户登录。

4.测试(双引号):
d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH24:MI:SS.FF"
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH24:MI:SS.FF TZH:TZM"
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SYSDATE             SYSTIMESTAMP                     LOCALTIMESTAMP
------------------- -------------------------------- ------------------------
2024-08-14 15:18:27 YYYY-MM-DD HH24:MI:SS.FF TZH:TZM YYYY-MM-DD HH24:MI:SS.FF

--//居然执行成功,如果没有做这个测试,也许就没有后面的许多事情,浪费大量的时间分析为什么?
--//很明显这样设置可以登录,我开始不理解的是为什么sysdate可以正常的输出时间,而SYSTIMESTAMP,LOCALTIMESTAMP却可以输出正常格
--//式.

--//于是我修改nls.bat脚本,在括号内加入一些内容:
d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set NLS_DATE_FORMAT="1234YYYY-MM-DD HH24:MI:SS"
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT="1234YYYY-MM-DD HH24:MI:SS.FF"
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT="1234YYYY-MM-DD HH24:MI:SS.FF TZH:TZM"
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SYSDATE             SYSTIMESTAMP                         LOCALTIMESTAMP
------------------- ------------------------------------ ----------------------------
2024-08-14 15:24:12 1234YYYY-MM-DD HH24:MI:SS.FF TZH:TZM 1234YYYY-MM-DD HH24:MI:SS.FF

SYSTEM@192.168.56.101:1521/book> show parameter nls_
PARAMETER_NAME           TYPE   VALUE
------------------------ ------ -----------------------------------------
nls_calendar             string GREGORIAN
nls_comp                 string BINARY
nls_currency             string $
nls_date_format          string YYYY-MM-DD HH24:MI:SS
nls_date_language        string AMERICAN
nls_dual_currency        string $
nls_iso_currency         string AMERICA
nls_language             string AMERICAN
nls_length_semantics     string BYTE
nls_nchar_conv_excp      string FALSE
nls_numeric_characters   string .,
nls_sort                 string BINARY
nls_territory            string AMERICA
nls_time_format          string HH24:MI:SSXFF
nls_time_tz_format       string HH24.MI.SSXFF TZH:TZM
nls_timestamp_format     string "1234YYYY-MM-DD HH24:MI:SS.FF"
nls_timestamp_tz_format  string "1234YYYY-MM-DD HH24:MI:SS.FF TZH:TZM"

--//于是我开始怀疑nls_date_format被重置了.直到我做了alter system set events '10046 trace name context forever, level 12';
--//整个过程略(浪费大量时间).是因为环境变量设置ORACLE_PATH,SQL_PATH,路径里面调用了tanel poder的tpt的login.sql脚本,
--//login.sql又调用init.sql.

d:\tmp\study> cat d:\tools\sqllaji\tpt\login.sql
cat d:\tools\sqllaji\tpt\login.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

-- calling init.sql which will set up sqlpus variables
@init.sql
-- i.sql is the "who am i" script which shows your session/instance info and
-- also sets command prompt window/xterm title
-- @i.sql

-- you can put your own login scripts here

d:\tmp\study> grep alter  d:\tools\sqllaji\tpt\init.sql
  alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

--//tpt的脚本初始化会重新设置nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set NLS_DATE_FORMAT="1234YYYY-MM-DD HH24:MI:SS"
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT="1234YYYY-MM-DD HH24:MI:SS.FF"
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT="1234YYYY-MM-DD HH24:MI:SS.FF TZH:TZM"
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l -R 3 system/bookbook@192.168.56.101:1521/book
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available

SYSDATE
-------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
1234YYYY-MM-DD HH24:MI:SS
1234YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
1234YYYY-MM-DD HH24:MI:SS.FF
--//给sqlplus 加入-R 3 参数避开@之类的调用.现在输出都出现了日期格式.
--//我在这里走了很大的弯路,最终定位环境变量设置ORACLE_PATH,SQL_PATH路径里面调用了tanel poder的tpt的login.sql脚本.
--//后面的测试我修改nls.bat脚本,重置ORACLE_PATH,SQLPATH=来规避这个问题,注意这样还是会执行oracle的
--//E:\tools\database21c\sqlplus\admin的glogin.sql的调用.
set ORACLE_PATH=
set SQLPATH=

5.如果使用1个双引号可以登录,尝试2个双引号呢?
--//使用两个双引号看看.实际上当时思维混乱,有点在乱尝试。
d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set ORACLE_PATH=
d:\tmp\study>set SQLPATH=
d:\tmp\study>set NLS_DATE_FORMAT=""YYYY-MM-DD HH24:MI:SS""
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT=""YYYY-MM-DD HH24:MI:SS.FF""
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT=""YYYY-MM-DD HH24:MI:SS.FF TZH:TZM""
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SYSDATE             SYSTIMESTAMP
------------------- --------------------------------------------------------------------------- -----------------------------------------
2024-08-14 15:47:21 2024-08-14 15:47:21.156656 +08:00                                           2024-08-14 15:47:21.156659
--//居然成功了.
--//注意1个细节.标题没有localtimestamp.遇到1个怪问题.
--//注:有时候输出太长,为了方便阅读,会做一些排版,缩小宽度,但是在这里我原样贴出.

d:\tmp\study>echo select systimestamp,localtimestamp,sysdate from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SYSTIMESTAMP                                                                LOCA
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-14 15:57:42.611420 +08:00                                           2024-08-14 15:57:42.611423                                                  2024-08-14 15:57:42
--//输出顺序换一下,sysdate到最后.
--//如果你使用vi定位到LOCA可以发现正常在80列.但是为什么这样不理解,我做spool也是这样.为什么?

SYSTEM@192.168.56.101:1521/book> column value format a40
SYSTEM@192.168.56.101:1521/book> select * from v$nls_parameters ;
PARAMETER                      VALUE                                        CON_ID
------------------------------ ---------------------------------------- ----------
NLS_LANGUAGE                   AMERICAN                                          1
NLS_TERRITORY                  AMERICA                                           1
NLS_CURRENCY                   $                                                 1
NLS_ISO_CURRENCY               AMERICA                                           1
NLS_NUMERIC_CHARACTERS         .,                                                1
NLS_CALENDAR                   GREGORIAN                                         1
NLS_DATE_FORMAT                ""YYYY-MM-DD HH24:MI:SS""                         1
NLS_DATE_LANGUAGE              AMERICAN                                          1
NLS_CHARACTERSET               ZHS16GBK                                          1
NLS_SORT                       BINARY                                            1
NLS_TIME_FORMAT                HH24:MI:SSXFF                                     1
NLS_TIMESTAMP_FORMAT           ""YYYY-MM-DD HH24:MI:SS.FF""                      1
NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZH:TZM                             1
NLS_TIMESTAMP_TZ_FORMAT        ""YYYY-MM-DD HH24:MI:SS.FF TZH:TZM""              1
NLS_DUAL_CURRENCY              $                                                 1
NLS_NCHAR_CHARACTERSET         AL16UTF16                                         1
NLS_COMP                       BINARY                                            1
NLS_LENGTH_SEMANTICS           BYTE                                              1
NLS_NCHAR_CONV_EXCP            FALSE                                             1
19 rows selected.
--//格式里面记录的是2个双引号。

6.有了双引号的先例,尝试2个单引号呢?

d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set ORACLE_PATH=
d:\tmp\study>set SQLPATH=
d:\tmp\study>set NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD HH24:MI:SS.FF''
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT=''YYYY-MM-DD HH24:MI:SS.FF TZH:TZM''
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SYSDATE               SYSTIMESTAMP
--------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
'2024-08-14 15:56:01' '2024-08-14 15:56:01.616523 +08:00'                                         '2024-08-14 15:56:01.616526'
--//SYSDATE,SYSTIMESTAMP,LOCALTIMESTAMP的输出包含引号.内容对的.
--//注意1个细节.标题没有localtimestamp.不理解.

SYSTEM@192.168.56.101:1521/book> column value format a40
SYSTEM@192.168.56.101:1521/book> select * from v$nls_parameters ;
PARAMETER                      VALUE                                        CON_ID
------------------------------ ---------------------------------------- ----------
NLS_LANGUAGE                   AMERICAN                                          1
NLS_TERRITORY                  AMERICA                                           1
NLS_CURRENCY                   $                                                 1
NLS_ISO_CURRENCY               AMERICA                                           1
NLS_NUMERIC_CHARACTERS         .,                                                1
NLS_CALENDAR                   GREGORIAN                                         1
NLS_DATE_FORMAT                'YYYY-MM-DD HH24:MI:SS'                           1
NLS_DATE_LANGUAGE              AMERICAN                                          1
NLS_CHARACTERSET               ZHS16GBK                                          1
NLS_SORT                       BINARY                                            1
NLS_TIME_FORMAT                HH24:MI:SSXFF                                     1
NLS_TIMESTAMP_FORMAT           'YYYY-MM-DD HH24:MI:SS.FF'                        1
NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZH:TZM                             1
NLS_TIMESTAMP_TZ_FORMAT        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'                1
NLS_DUAL_CURRENCY              $                                                 1
NLS_NCHAR_CHARACTERSET         AL16UTF16                                         1
NLS_COMP                       BINARY                                            1
NLS_LENGTH_SEMANTICS           BYTE                                              1
NLS_NCHAR_CONV_EXCP            FALSE                                             1
19 rows selected.

SYSTEM@192.168.56.101:1521/book> select "DUMMY",'DUMMY' c10 from "DUAL";
D C10
- ----------
X DUMMY
--//单引号定义的是字符串,而双引号定义的字段.

7.继续探究:
--//突然想环境变量NLS参数的定义相当于使用特定的格式输出.可以理解为使用了函数to_char来格式化输出,测试并且验证看看我的判
--//断是否正确.

SYSTEM@192.168.56.101:1521/book> select to_char(localtimestamp,''YYYY-MM-DD HH24:MI:SS.FF'') c30 from dual;
SP2-0552: Bind variable "MI" not declared.
--//单个引号,报SP2-0552: Bind variable "MI" not declared.

SYSTEM@192.168.56.101:1521/book> select to_char(localtimestamp,''YYYY-MM-DD HH24MISS.FF'') c30 from dual;
select to_char(localtimestamp,''YYYY-MM-DD HH24MISS.FF'') c30 from dual
                                *
ERROR at line 1:
ORA-00907: missing right parenthesis
--//单个引号,取消:,还是报错.说明使用单个引号会报错.

SYSTEM@192.168.56.101:1521/book> select to_char(localtimestamp,'"YYYY-MM-DD HH24:MI:SS.FF"') c30 from dual;
C30
------------------------------
YYYY-MM-DD HH24:MI:SS.FF
--//单个双引号,输出日期格式,感觉自己判断是对的.

SYSTEM@192.168.56.101:1521/book> select to_char(localtimestamp,'""YYYY-MM-DD HH24:MI:SS.FF""') c30 from dual;
C30
------------------------------
2024-08-14 16:17:24.995349

SYSTEM@192.168.56.101:1521/book> select '""1234abc""' c30 from dual;
C30
------------------------------
""1234abc""
--//2个双引号,输出正确,与前面测试一样.但是我不理解.....

SYSTEM@192.168.56.101:1521/book> select to_char(localtimestamp,'''YYYY-MM-DD HH24MISS.FF''') c30 from dual;
C30
------------------------------
'2024-08-14 161952.893218'
--//2个单引号,可以理解里面''解析为1个单引号,这样结果输出结果带有单引号.

8.奇葩的例子:
--//在windows下定义环境变量不需要使用单双引号,不像linux的定义环境变量字串有空格,一定要使用单双引号,例子:
$ export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
-bash: export: `HH24:MI:SS': not a valid identifier

$ export NLS_DATE_FORMAT=YYYY-MM-DD:HH24:MI:SS
--//没有空格没事!!

--//如果那位在windows下定义环境变量结尾暗藏空格呢?
d:\tmp\study>cat -Ev nls.bat
rem @ echo off^M$
set ORACLE_PATH=^M$
set SQLPATH=^M$
set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS                  ^M$
~~~~~~~~~~~~~~
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK^M$
set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF^M$
set NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM^M$
echo select sysdate,systimestamp,localtimestamp from dual ; | sqlplus -s -l system/bookbook@192.168.56.101:1521/book^M$
--//下划线哪行S后面有18个空格.

d:\tmp\study>nls
d:\tmp\study>rem @ echo off
d:\tmp\study>set ORACLE_PATH=
d:\tmp\study>set SQLPATH=
d:\tmp\study>set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
d:\tmp\study>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
d:\tmp\study>set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
d:\tmp\study>set NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
d:\tmp\study>echo select sysdate,systimestamp,localtimestamp from dual ;   | sqlplus -s -l system/bookbook@192.168.56.101:1521/book
SP2-0642: SQL*Plus internal error state 2021, context 1:1801:0
Unsafe to proceed
kgepop: no error frame to pop to for error 1801
kpedbg_dmp_stack<-kpedbgdmp<-kgerinv_internall<-kgerinv<-kgerin<-kgepop<-kgesecl0<-kolderr<-ldxsnf<-ldxsti<-ldxbegin<-ldxini<-kpuuini..0<-kpuinit0<-kpuenvcr<-OCIEnvCreate<-OCIEnvCreate<-afioci<-aficntini<-afidrv<-main<-?__scrt_common_main_seh@@YAHXZ<-0x00007FFCD9987034<-0x00007FFCDB2E26A1
kpedbg_dmp_stack<-kpedbgdmp<-kgepop<-kgesecl0<-kolderr<-ldxsnf<-ldxsti<-ldxbegin<-ldxini<-kpuuini..0<-kpuinit0<-kpuenvcr<-OCIEnvCreate<-OCIEnvCreate<-afioci<-aficntini<-afidrv<-main<-?__scrt_common_main_seh@@YAHXZ<-0x00007FFCD9987034<-0x00007FFCDB2E26A1

--//如果有17个空格,就不会报错.可以简单验证:
SYS@192.168.56.101:1521/book> alter session set nls_date_format='YYYY:MM:DD HH24:MI:SS                  ';
ERROR:
ORA-01801: date format is too long for internal buffer

SYS@192.168.56.101:1521/book> alter session set nls_date_format='YYYY:MM:DD HH24:MI:SS                 ';
ession altered.
--//后面有17个空格.

SYSTEM@192.168.56.101:1521/book> select sysdate from dual ;
SYSDATE
------------------------------------
2024:08:14 16:34:15

--//不过这个问题如果那位在环境变量里定义这样的情况,问题就很难查了.
--//提醒一下:实际上sqlplus有提示:
SP2-0642: SQL*Plus internal error state 2021, context 1:1801:0
kgepop: no error frame to pop to for error 1801
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//里面1801就是提示.

$ oerr ora 1801
01801, 00000, "date format is too long for internal buffer"
// *Cause:
// *Action:
--//在这样的情况我使用toad程序直接退出,根本没有任何提示.

9.总结:
--//写这么多,感觉自己把问题想的复杂了,总之在windows下定义oracle环境变量不要单双引号就ok了.
--//windows下环境变量赋值相当于整个作为字符串传入变量,比如结尾的空格也作为变量的一部分.
--//linux下不行,因为字符串里面包含空格,必须使用单双引号,并且不作为变量的一部分.
--//有机会测试linux下上面各种赋值的情况.

--//测试时走了弯路,没有规避loing.sql的影响,导致被输出结果给带偏了,浪费大量的时间.

--//不过我还是无法理解使用两个双引号的情况,还有就是sqlplus的标题丢失问题.
--//关于标题问题只能称为oracle的灵异事情,另外写一篇blog分析.

热门相关:腹黑老公,请离婚!   我能改变东西颜色   特工皇后不好惹   萌妻鲜嫩:神秘老公夜夜宠   萌妻鲜嫩:神秘老公晚上见