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