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

来源：这里教程网时间：2026-03-03 20:32:20 作者：

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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推