[20211231]set linesize and dbms_output.line输出问题.txt

--//昨天改写sql_id.sql脚本,主要原因有几个.
1.开发写代码不规范,里面拚接的字符使用\r,没有\n,导致sqlplus的输出乱码,多次建议开发替换纠正这个错误,没用.
2.剩下的两个问题是我最近遇到的,不知道那个开发写sql代码都是一行,而且字段与字段之间还有很多空格,导致语句很长.
导致我抽取后给格式化,然后在考虑优化问题.我甚至要设置set linesize 10000,不然会出现换行问题.导致抽取的语句
无法执行.
--//补充一点:我个人认为不是懒,而是职业素养不够,真不知道这位开发那个学校毕业的.
3.另外一个问题,也是不知道那位开发写sql代码在每个字段输出的后面,写入大量注解说明.这样即使一条很短的sql语句也被他写的很长,
总之我最近遇到的问题主要都是超长sql语句的问题.

--//而我在使用sql_id抽取时,遇到奇怪问题,我自己也没有想到与set linesize设置以及dbms_output.line输出有关,通过例子说明问题.

1.环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.写一个长超的sql语句:
--//注意sqlplus每行有2499 characters限制.
SCOTT@book> @ b1.txt
Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
SP2-0734: unknown command beginning "sdasd asda..." - rest of line ignored.

$ cat bb1.txt
select
sysdate
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
--//38 line snip
--012345678901234567890123456789012345678901234567890123456789012345678901234567890ZY34567890123456
--//9 line snip
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
from dual ;

--//注1行99个字符,加上结尾的\n,unix文件格式,正好每行100个字符.
--//注意一串数字里面有两个字符ZY，正好在4000，4001的位置。
--//定位4000位置在vim下使用:goto 4000,使用g ctrl+g 显示位置。

3.测试:
$ cat sql_id.sql
SET LINESIZE 4000
VAR V_SQL_FULLTEXT CLOB
COL SQL_FULLTEXT FOR A4000 WORD_WRAP

SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON FORMAT TRUNCATED

PROMPT
PROMPT --SQL_ID = &&1

DECLARE
    V_SQL_FULLTEXT   CLOB;
    V_COUNT          NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE (' ');
    SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;
    IF  V_COUNT=1
    THEN
        SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
    ELSE
        SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
        IF  V_COUNT=1
        THEN
            SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
        END IF;
    END IF;
    DBMS_OUTPUT.PUT_LINE (' ');
    EXCEPTION WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

SET SERVEROUTPUT OFF
SET FEEDBACK 6
SET LINESIZE 277

4.测试:

SCOTT@book> @ bb1.txt
SYSDATE--0123456789
-------------------
2021-12-31 09:34:09

SCOTT@book> @hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
 879656348 aahvby0u6wzcw            0      32156      1388734953 2021-12-31 09:34:08    16777216

SCOTT@book> @sql_id aahvby0u6wzcw
--SQL_ID = aahvby0u6wzcw

select
sysdate
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
--// 38 line snip
--012345678901234567890123456789012345678901234567890123456789012345678901234567890ZY

--//你可以发现显示仅仅在4001位置。
--//我必须设置SET LINESIZE 32767，才能完全输出。编写时在这里浪费一点点时间，有点奇怪的是我以前写的
--//http://blog.itpub.net/267265/viewspace-1700792/ -> [20150616]Creating Test Script With Bind Variable.txt
--//已经更新多次，好像没有遇到这类问题，我还给仔细探究。
--//我仔细看了脚本，里面的设置是set linesize 4000，理论讲也应该遇到我测试发现的问题，奇怪我应该已经遇到超过4000字符的sql
--//语句，为什么以前没有出现呢？继续测试：

SCOTT@book> @ b8 aahvby0u6wzcw 0 1
...

--//我看到抽取的执行语句如下：
select
sysdate
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
--// 38 line snip
--012345678901234567890123456789012345678901234567890123456789012345678901234567890ZY
34567890123456
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
--// 9 line snip
--0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
from dual;

--//是有输出，不过换了行罢了。
--//最终我发现问题在
SET SERVEROUTPUT ON FORMAT TRUNCATED
--//修改为
SET SERVEROUTPUT ON FORMAT WRAP
--//问题解决。哎，学艺不精啊。

--//因为要使用dbms_output.put_line输出行，参考连接
--//http://blog.itpub.net/267265/viewspace-2743228/ => [20201218]dbms_output.put_line无法输出前面的空格.txt

--//最终我修改了sql_id,b8脚本，选择set linesize 32767，使用prompt输出一空行。
$ cat sql_id.sql

SET LINESIZE 32767
--SET LINESIZE 4000
VAR V_SQL_FULLTEXT CLOB
COL SQL_FULLTEXT FOR A4000 WORD_WRAP

SET FEEDBACK OFF
SET SERVEROUTPUT ON

PROMPT
PROMPT --SQL_ID = &&1
PROMPT

DECLARE
    V_SQL_FULLTEXT   CLOB;
    V_COUNT          NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;
    IF  V_COUNT=1
    THEN
        SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
    ELSE
        SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
        IF  V_COUNT=1
        THEN
            SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
        END IF;
    END IF;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

PROMPT
SET SERVEROUTPUT OFF
SET FEEDBACK 6
SET LINESIZE 277

--//没有想到问题出在我设置SET SERVEROUTPUT ON FORMAT TRUNCATED。