[20211231]set linesize and dbms_output.line輸出問題.txt

[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。