[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 ( http://asktom.oracle.com )
--          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