[20211126]完善tpt pr.sql指令碼.txt

[20211126]完善tpt pr.sql指令碼.txt

--//tpt提供pr.sql指令碼把原來橫向輸出的內容變成縱向輸出，便於閱讀。但是有一個小問題，透過例子說明：

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.測試：
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        44       1574 35220                    DEDICATED 35221       27        146 alter system kill session '44,1574' immediate;

SCOTT@book> @ usid 44
USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID         HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ ------------- ----------- ---------- -------- ---------------- ---------------- ----------------
LOGON_TIME
-------------------
SCOTT                    '44,1574'        18290945 oracle           xxxxxxdg4           (TNS V1-V3)          35221              27 35220                    9r6m4c0hpg6dx   559389117          0 ACTIVE   000000008638EC10 000000008620F338
2021-11-26 11:12:29

--//輸出內容太多換行了。

SCOTT@book> @ pr
ERROR:
ORA-01756: quoted string not properly terminated

--//出現ora-01756錯誤。檢查pr.sql指令碼，發現使用\字元作為分隔。
0 c clob := q'\
0 declare

999999      \';;

$ grep '\\' usid.sql
       substr(s.machine,instr(s.machine,'\')) u_machine,
--//而usid指令碼里面正好也有字元\.這樣導致分隔報錯。這樣修改pr.sql選擇一個不常用的字元作為分隔就ok了。選擇那個呢？
--//實際上任何可見的字元都可能出現問題，很簡單選擇一個不可見字元ctrl+g(小喇叭發生聲音，一般程式碼不會出現)或者ctrl+f作為分隔.
--//在linux 的vim下透過ctrl+v ctrl+f輸入。

SCOTT@book> @ pr
PL/SQL procedure successfully completed.

--//嗯，沒有輸出。檢視程式碼可以發現usid.sql指令碼里面有&1要替換，修改如下執行：

SCOTT@book> @ pr 44
==============================
U_USERNAME                    : SCOTT
U_SID                         :  '44,1574'
U_AUDSID                      : 18290945
U_OSUSER                      : oracle
U_MACHINE                     : xxxxxxdg4
U_PROGRAM                     : (TNS V1-V3)
U_SPID                        : 35221
U_PID                         : 27
CPID                          : 35220
SQL_ID                        : 2jwdj6msnrx81
USID_SQL_HASH_VALUE           : 4048286977
LASTCALL                      : 0
STATUS                        : ACTIVE
SADDR                         : 000000008638EC10
PADDR                         : 000000008620F338
TADDR                         :
LOGON_TIME                    : 2021-11-26 11:12:29
PL/SQL procedure successfully completed.

--//OK。

$ cat -vs pr.sql
.
-- Notes:   This script is based on Tom Kyte's original printtbl code ( )
--          For coding simplicity (read: lazyness) I'm using custom quotation marks ( q'\ ) so
--          this script works only from Oracle 10gR2 onwards

def _pr_tmpfile=&_tpt_tempdir/pr_&_tpt_tempfile..tmp

@@saveset
set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on

0 c clob := q'^F
0 declare

999999      ^F';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

@@loadset

get &_pr_tmpfile nolist
host &_delete &_pr_tmpfile

--//注意開頭的. 可不是多餘的。