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

[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说明该问题.