[20240815]oracle 21c NLS_DATE_FORMAT设置问题(linux).txt

--//完善整个这方面的测试,看看linux作为客户端的情况.

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.建立测试脚本:
$ cat nls.sh
#! /bin/bash
export ORACLE_PATH=
export SQLPATH=

export NLS_DATE_FORMAT=""YYYY-MM-DD HH24:MI:SS""
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_TIMESTAMP_FORMAT=""YYYY-MM-DD HH24:MI:SS.FF""
export 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

--//1个单引号或者1个双引号的情况就不做测试了.

3.测试(2个双引号):
$ . nls.sh
-bash: export: `HH24:MI:SS': not a valid identifier
-bash: export: `HH24:MI:SS.FF': not a valid identifier
-bash: export: `HH24:MI:SS.FF': not a valid identifier
-bash: export: `TZH:TZM': not a valid identifier

SYSDATE    SYSTIMESTAMP
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2024-08-15 2024-08-15                                                                  2024-08-15

$ echo $NLS_DATE_FORMAT
YYYY-MM-DD
--//相当于空格后面的字符串丢失了.
--//注意标题还是消失.

4.测试(2个单引号号):
$ cat nls.sh
#! /bin/bash
export ORACLE_PATH=
export SQLPATH=

export NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD HH24:MI:SS.FF''
export 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.sh
-bash: export: `HH24:MI:SS': not a valid identifier
-bash: export: `HH24:MI:SS.FF': not a valid identifier
-bash: export: `HH24:MI:SS.FF': not a valid identifier
-bash: export: `TZH:TZM': not a valid identifier

SYSDATE    SYSTIMESTAMP
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2024-08-15 2024-08-15                                                                  2024-08-15

$ echo $NLS_DATE_FORMAT
YYYY-MM-DD

--//相当于空格后面的字符串丢失了.

5.测试(如果要产生windows下2个单引号的效果):
$ cat nls.sh
#! /bin/bash
export ORACLE_PATH=
export SQLPATH=

export NLS_DATE_FORMAT="''YYYY-MM-DD HH24:MI:SS''"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_TIMESTAMP_FORMAT="''YYYY-MM-DD HH24:MI:SS.FF''"
export 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.sh

SYSDATE               SYSTIMESTAMP
--------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
'2024-08-15 09:10:47' '2024-08-15 09:10:47.058265 +08:00'                                         '2024-08-15 09:10:47.058266'

6.测试(如果要产生windows下2个双引号的效果):
 $ cat nls.sh
#! /bin/bash
export ORACLE_PATH=
export SQLPATH=

export NLS_DATE_FORMAT='""YYYY-MM-DD HH24:MI:SS'""
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_TIMESTAMP_FORMAT='""YYYY-MM-DD HH24:MI:SS.FF'""
export 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.sh
SYSDATE             SYSTIMESTAMP
------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2024-08-15 09:12:36 2024-08-15 09:12:36.444169 +08:00                                           2024-08-15 09:12:36.444170

7.总结:
--//测试完成,不再说明细节.
--//注:标题问题,是set emb on导致,为什么我不知道,另外写blog说明该问题.