[20240815]为什么标题显示不全2.txt

--//前几天测试NLS环境变量遇到的问题,就是标题仅仅显示一部分,似乎在80列后被截断,分析看看.
--//由于在linux下也遇到类似问题,测试分析在linux下完成.

1.环境:
SYS@book> @pr
==============================
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.问题提出:
--//首先取消SQLPATH,ORACLE_PATH的设置,避免访问相关执行目录里面的login.sql脚本.
$ export SQLPATH=
$ export ORACLE_PATH=
--//建议glogin.sql就一行内容set linesize 250.

$ echo 'select systimestamp,localtimestamp,sysdate from dual ;' | sqlplus -s -l  system/bookbook@192.168.56.101:1521/book
SYSTIMESTAMP                                                                LOCA
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-15 09:19:28.765628 +08:00                                           2024-08-15 09:19:28.765629                                                  2024-08-15 09:19:28
--//问题再现.localtimestamp仅仅显示到LOCA,正好80列的位置.
--//注:这样执行还是访问$ORACLE_HOME目录下的sqlplus/admin/glogin.sql脚本.

$ echo 'select systimestamp,localtimestamp,sysdate 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

SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSDATE
-------------------
2024-08-15 09:21:43.276103 +08:00
2024-08-15 09:21:43.276105
2024-08-15 09:21:43
--//加入-R 3 ,虽然会访问sqlplus/admin/glogin.sql脚本,导致gloin.sql就2行,-R 3 不会以@或者@@形式调用执行sql脚本.
--//@@zzlogin.sql
--//@@128.txt
--//这样显示宽度仅仅缺省80列.修改如下:

$ echo 'set linesize 180;^Jselect systimestamp,localtimestamp,sysdate 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

SYSTIMESTAMP                                                                LOCALTIMESTAMP                                                              SYSDATE
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-15 09:29:26.968017 +08:00                                           2024-08-15 09:29:26.968019                                                  2024-08-15 09:29:26

--//里面^J在linux下按ctrl+v,ctrl+j.加入-R 3 反而正常了.说明gloing.sql执行的一些设置导致了这样的情况?

$ rlwrap sqlplus  -s -l / as sysdba
select systimestamp,localtimestamp,sysdate from dual ;

SYSTIMESTAMP                                                                LOCA
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-15 09:31:51.272960 +08:00                                           2024-08-15 09:31:51.272963                                                  2024-08-15 09:31:51

select systimestamp,localtimestamp,sysdate from dual ;
SYSTIMESTAMP                                                                LOCALTIMESTAMP                                                              SYSDATE
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-15 09:32:18.588185 +08:00                                           2024-08-15 09:32:18.588188                                                  2024-08-15 09:32:18

quit

--//再次出现奇怪的现象,第1次执行异常,第2次执行正常,指标题.
--//很明显问题出在sqlplus/admin/glogin.sql执行了什么?
--//我的glogin.sql就2行:
@@zzlogin.sql
@@128.txt

--//128.txt文件全部是column语句.
--//检查zzlogin.sql,我一行一行注解测试,除了set line 250外,发现当我注解set emb on pages 9999时,问题消失.

$ echo 'set linesize 180;^Jselect systimestamp,localtimestamp,sysdate from dual ;' | sqlplus -s -l  system/bookbook@192.168.56.101:1521/book
SYSTIMESTAMP                                                                LOCALTIMESTAMP                                                              SYSDATE
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2024-08-15 09:39:35.872051 +08:00                                           2024-08-15 09:39:35.872052                                                  2024-08-15 09:39:35

--//可以确定位置在set emb on上面,设置set emb off pages 9999问题消失.
--//现在问题看看set EMBEDDED on表示怎么意思?

SET EMB [EDDED] Is the command, which may be abbreviated SET EMB. ON Turns the embedded report feature on. Executing a
SELECT statement will not force a page break, nor will it reset the page number. OFF Turns the embedded report feature
off. Executing a SELECT statement will force a page break, and the page number will be reset to 1.

--//做一个简单例子:
SYS@book> set emb on pagesize 5
SYS@book> select level from dual connect by level<=6;
     LEVEL
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

--//退出再测试:
SYS@book> set emb off pagesize 5
SYS@book> select level from dual connect by level<=6;

     LEVEL
----------
         1
         2

     LEVEL
----------
         3
         4

     LEVEL
----------
         5
         6

6 rows selected.
--//例子已经很明显说明两种的区别.
--//注:如果设置set emb off后不退出,再次set emb on pagesize 5,好像不会生效,我不知道为什么?

--//个人的感觉这就是一个bug,设置set emb on时第一次显示限制列显示宽度80列.而且就是第一次执行出现,第2次问题不再出现.

--//另外如果设置SQLPATH,ORACLE_PATH环境变量,在linux下21c的sqlplus设置ORACLE_HOME有效,或者$ORACLE_HOME目录下sqlplus/admin
--//里面的glogin.sql如果有sql语句执行,无论如何问题不能再现,我测试环境启动sqlplus会自动调用tpt/loing.sql,然后调用
--//tpt/init.sql文件.这种情况下问题不会再现.