[20250320]DBMS_OUTPUT.PUT_LINE输出限制.txt

--//去年遇到的问题，一直没时间看。生产系统遇到的问题。

1.环境:
SYS@127.0.0.1:9105/xtdb/xtdb1> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.问题提出:

SYS@127.0.0.1:9105/xtdb/xtdb1> @ ashtop sql_id 1=1  &day
    Total                                                                         Distinct Distinct    Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
    56967      .7   61% |               2024-12-25 08:45:13 2024-12-26 08:45:08          2    48165       48165
     2014      .0    2% | 6wcywxctbbvs8 2024-12-25 08:50:53 2024-12-26 08:41:17        283     2014         283
      697      .0    1% | 0za9fv0j1vgkk 2024-12-25 08:45:25 2024-12-26 08:43:02        682      696         684
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      613      .0    1% | 41a4d5z9cswza 2024-12-25 08:51:02 2024-12-26 08:41:19        280      603         280
      584      .0    1% | 0315jsvndf9s1 2024-12-25 08:45:47 2024-12-26 03:06:18        582      584         584
      526      .0    1% | d7sa2ga44303r 2024-12-25 08:51:13 2024-12-26 08:41:24        278      526         278
      521      .0    1% | fnxvn2huxfy5y 2024-12-25 08:51:11 2024-12-26 08:41:23        276      521         276
...

SYS@127.0.0.1:9105/xtdb/xtdb1> @ sql_id 0za9fv0j1vgkk
-- SQL_ID = 0za9fv0j1vgkk come from shared pool
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
--//直接使用我写的sql_id获取sql语句报错.执行查看这条语句来自MMON_SLAVE模块，超级长,而且似乎没有换行.
--//仔细检查才发现是DBMS_OUTPUT.PUT_LINE输出限制导致的问题。

3.简单验证：
$ uniq -c a.txt
      1 select /*+
    327 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
      1 00000000000000000000000000000000000
      1 */ sysdate from dual;

$ ls -l a.txt
-rw-r--r--. 1 oracle oinstall 32769 2025-03-20 16:44:31 a.txt
--//如果不包括最后分号以及回车，大小32767.

SCOTT@book01p> @ a.txt
SYSDATE
-------------------
2025-03-20 16:45:54

SCOTT@book01p> @ hashz

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
 456068362 buzxt98dky38a            0      68874  1b2f0d0a  2025-03-20 16:45:53    16777216

SCOTT@book01p> @ sql_id buzxt98dky38a
-- SQL_ID = buzxt98dky38a come from shared pool

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
--//报错。如果删除a.txt 1个字符，执行后使用写的sql_id.sql脚本查询就不会报错。
$ wc  a.txt
  330   334 32768 a.txt

--//执行就不会报错,测试略。
--//另外一个简单验证。

SCOTT@book01p> variable v clob;
SCOTT@book01p> exec :v := lpad('x',32767,'x')||'y';

PL/SQL procedure successfully completed.

SCOTT@book01p> exec DBMS_OUTPUT.PUT_LINE(:v);
BEGIN DBMS_OUTPUT.PUT_LINE(:v); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

--//实际上DBMS_OUTPUT.PUT_LINE的参数是VARCHAR2，不是clob类型。
SCOTT@book01p> @ desc_proc sys DBMS_OUTPUT PUT_LINE
SCOTT@book01p>  @pr
==============================
OWNER                         : SYS
PACKAGE_NAME                  : DBMS_OUTPUT
OBJECT_NAME                   : PUT_LINE
SEQUENCE                      : 1
ARGUMENT_NAME                 : A
DATA_TYPE                     : VARCHAR2
IN_OUT                        : IN
DEFAULTED                     : N
PL/SQL procedure successfully completed.

--//一般sql语句很少超过32K，放弃修改。