[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分析.

© 版权声明
THE END
支持一下吧
点赞8 分享
评论 抢沙发
头像
请文明发言!
提交
头像

昵称

取消
昵称表情代码快捷回复

    暂无评论内容