[20221126]tpt pr.sql指令碼執行問題.txt

[20221126]tpt pr.sql指令碼執行問題.txt

--//昨天在使用tpt pr.sql指令碼時遇到一些問題,今天在家裡做一些分析看看:

1.環境:
SYS@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

$ cat a.txt
SELECT UPPER(NVL(PROGRAM, 'null'))
         , UPPER(MODULE)
         , TYPE
         , DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1))
         , OSUSER
         , MACHINE
         , SCHEMANAME
         , USERNAME
         , SERVICE_NAME
         , SID
         , SERIAL#
  FROM SYS.V_$SESSION
 WHERE SID = SYS_CONTEXT('userenv', 'sid');

2.測試:
SYS@test01p> @ a.txt
...
--//執行輸出省略.

SYS@test01p> @ prxx_win
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 24

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 43
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 24

--//注如果是linux下使用pr.sql執行,報如下錯誤:
> @ pr
ORA-06512: at "SYS.DBMS_SQL", line 2251
ORA-06512: at line 12

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow, col_name_len=92. Use describe_columns2
ORA-06512: at line 31

--//提示使用dbms_sql.describe_columns2代替.而且提示也很明顯
DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1))
--//做作為欄位太長.刪除空格,在vim下執行:.s/ //g
DECODE(NVL(INSTR(PROCESS,':'),0),0,NVL(PROCESS,1234),SUBSTR(PROCESS,1,INSTR(PROCESS,':')-1))
--//這樣正好92個字元.與提示對應上,要不改pr指令碼執行,給欄位起一個別名就ok了.
--//修改如下,後面加入 aa作為欄位名:
, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)) aa

SYS@test01p> @ a.txt
SYS@test01p> @ prxx_win
==============================
UPPER(NVL(PROGRAM,'NULL'))    : SQLPLUS.EXE
UPPER(MODULE)                 : SQL*PLUS
TYPE                          : USER
AA                            : 8860
OSUSER                        : XXXX\Administrator
MACHINE                       : WORKGROUP\XXXX
SCHEMANAME                    : SYS
USERNAME                      : SYS
SERVICE_NAME                  : test01p
SID                           : 18
SERIAL#                       : 11582
PL/SQL procedure successfully completed.

3.如果修改指令碼呢?
--//這樣要修改兩處,dbms_sql.desc_tab使用 dbms_sql.desc_tab2代替,dbms_sql.describe_columns 使用
--//dbms_sql.describe_columns2代替. 取消欄位別名aa,繼續測試:

SYS@test01p> @ a.txt
SYS@test01p> @ prxx_win
==============================
UPPER(NVL(PROGRAM,'NULL'))    : SQLPLUS.EXE
UPPER(MODULE)                 : SQL*PLUS
TYPE                          : USER
DECODE(NVL(INSTR(PROCESS,':'),: 8860
OSUSER                        : XXXX\Administrator
MACHINE                       : WORKGROUP\XXXX
SCHEMANAME                    : SYS
USERNAME                      : SYS
SERVICE_NAME                  : test01p
SID                           : 18
SERIAL#                       : 11582
PL/SQL procedure successfully completed.
--//OK,這樣問題解決.

3.繼續分析:
--//如果你開啟包dbms_sql的定義可以發現如下內容:

  type desc_rec2 is record (
        col_type            binary_integer := 0,
        col_max_len         binary_integer := 0,
        col_name            varchar2(32767) := '',
        col_name_len        binary_integer := 0,
        col_schema_name     varchar2(32)   := '',
        col_schema_name_len binary_integer := 0,
        col_precision       binary_integer := 0,
        col_scale           binary_integer := 0,
        col_charsetid       binary_integer := 0,
        col_charsetform     binary_integer := 0,
        col_null_ok         boolean        := TRUE);
  type desc_tab2 is table of desc_rec2 index by binary_integer;

--//欄位名的長度最大可以到32767.如果你使用tpt pr.sql指令碼遇到類似問題,可以修改指令碼.
--//或者建立一個新的pr2.sql指令碼,遇到問題使用它來代替pr.sql指令碼.

--//再次提醒:裡面的^F在vim for windows版本使用ctrl+q ctrl+F代替,vim for linux版本使用ctrl+v ctrl+f代替.
--//另外pr2.sql指令碼開頭的. 不是多餘的.

$ cat -vs pr2.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_tab2;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns2( 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