[20251014]建立完善通用的prx.sql脚本.txt

[20251014]建立完善通用的prx.sql脚本.txt --//前几天更改了tpt的prr.sql，想实现一个更加通用pr.sql的版本。自己做一些尝试： --//参数1支持2种格式，第1种格式使用数字序列使用,分开,输出对应字段。第2种格式使用正则表达式输出对应字段. --//参数2支持参数2|n，2表示使用dbms_sql.desc_tab2。n 输出带字段的顺序号， $ cat -v prx.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 def _set_tmpfile=&_tpt_tempdir/set_&_tpt_tempfile..sql @@saveset set serverout on size 1000000 termout off save &_pr_tmpfile replace col tpt_pr   new_value _tpt_pr  format a10 col tpt_pr2  new_value _tpt_pr2  format a10 col tpt_prn  new_value _tpt_prn  format a10 col tpt_prnn new_value _tpt_prnn  format a10 col tpt_pri  new_value _tpt_pri  format a10 col tpt_prr  new_value _tpt_prr  format a10 col 1 new_value 1 col 2 new_value 2 SELECT NULL "1", NULL "2" FROM DUAL WHERE 1 = 2; SELECT CASE WHEN INSTR (LOWER ('&2'), '2') > 0 THEN '--' ELSE '  ' END tpt_pr       ,CASE WHEN INSTR (LOWER ('&2'), '2') > 0 THEN '  ' ELSE '--' END tpt_pr2       ,CASE WHEN INSTR (LOWER ('&2'), 'n') > 0 THEN '--' ELSE '  ' END tpt_prnn       ,CASE WHEN INSTR (LOWER ('&2'), 'n') > 0 THEN '  ' ELSE '--' END tpt_prn       ,CASE WHEN INSTR (LOWER ('&1'), ',') > 0 THEN '  ' WHEN '&1' IS NULL THEN '--' ELSE '--' END tpt_pri       ,CASE WHEN INSTR (LOWER ('&1'), ',') > 0 THEN '--' WHEN '&1' IS NULL THEN '--' ELSE '  ' END tpt_prr   FROM DUAL; set termout on get &_pr_tmpfile nolist . 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      &_tpt_pr  l_descTbl       dbms_sql.desc_tab;; 999999      &_tpt_pr2 l_descTbl       dbms_sql.desc_tab2;; 999999      l_colCnt        number;; 999999  begin 999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );; 999999      &_tpt_pr  dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );; 999999      &_tpt_pr2 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              &_tpt_prr if regexp_like(lower(l_descTbl(i).col_name), lower('&1')) then 999999              &_tpt_pri if  i in (&1) then 999999                  dbms_sql.column_value( l_theCursor, i,l_columnValue );; 999999                  &_tpt_prnn dbms_output.put_line ( rpad( l_descTbl(i).col_name,30 ) || ': ' || l_columnValue );; 999999                  &_tpt_prn  dbms_output.put_line ( lpad(i,3,'0')||' '||rpad( l_descTbl(i).col_name,30 ) || ': ' || l_columnValue );; 999999              &_tpt_pri end if;; 999999              &_tpt_prr end if;; 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;; / set serverout off term on @@loadset get &_pr_tmpfile nolist host &_delete &_pr_tmpfile &_set_tmpfile --//注：在get与host两行之际最后存在1个空行,里面的^F在vim下按ctrl+v，ctrl+f输入。 2.简单测试： $ cat tt.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'); SCOTT@book01p> @ tt.txt UPPER(NVL(PROGRAM,'NULL'))                                                           UPPER(MODULE)                                                    TYPE                           DECODE(NVL(INSTR(PROCESS,':'),0),0,NVL(PROCESS,1 OSUSER ------------------------------------------------------------------------------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ------------------------------ MACHINE              SCHEMANAME                     USERNAME                       SERVICE_NAME                          SID    SERIAL# -------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- SQLPLUS@CENTTEST (TNS V1-V3)                                                         SQL*PLUS                                                         USER                           3566                                             oracle centtest             SCOTT                          SCOTT                          book01p                               146      53724 SCOTT@book01p> @ pr ORA-06512: at "SYS.DBMS_SQL", line 2129 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 2129 ORA-06512: at line 24 --//直接执行pr报错，因为DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, --//':') - 1)没有定义别名，字段名太长，程序报错，要使用dbms_sql.desc_tab2. SCOTT@book01p> @ prx '' 2 ============================== UPPER(NVL(PROGRAM,'NULL'))    : SQLPLUS@CENTTEST (TNS V1-V3) UPPER(MODULE)                 : SQL*PLUS TYPE                          : USER DECODE(NVL(INSTR(PROCESS,':'),: 3566 OSUSER                        : oracle MACHINE                       : centtest SCHEMANAME                    : SCOTT USERNAME                      : SCOTT SERVICE_NAME                  : book01p SID                           : 146 SERIAL#                       : 53724 PL/SQL procedure successfully completed. SCOTT@book01p> @ prx name$ 2 ============================== SCHEMANAME                    : SCOTT USERNAME                      : SCOTT SERVICE_NAME                  : book01p PL/SQL procedure successfully completed. --//输出name结尾的字段名。 SCOTT@book01p> @ prx name$ 2n ============================== 007 SCHEMANAME                    : SCOTT 008 USERNAME                      : SCOTT 009 SERVICE_NAME                  : book01p PL/SQL procedure successfully completed. --//参数2加入n，支持输出字段顺序号。 SCOTT@book01p> @ prx 7,8,9 2n ============================== 007 SCHEMANAME                    : SCOTT 008 USERNAME                      : SCOTT 009 SERVICE_NAME                  : book01p PL/SQL procedure successfully completed. --//参数1使用数字序列，输出7，8，9字段信息。 SCOTT@book01p> select * from v$database   2  @ prx ^dbid|supp n ============================== 001 DBID                          : 1617337831 030 SUPPLEMENTAL_LOG_DATA_MIN     : YES 031 SUPPLEMENTAL_LOG_DATA_PK      : NO 032 SUPPLEMENTAL_LOG_DATA_UI      : NO 040 SUPPLEMENTAL_LOG_DATA_FK      : NO 041 SUPPLEMENTAL_LOG_DATA_ALL     : NO 052 SUPPLEMENTAL_LOG_DATA_PL      : NO 059 SUPPLEMENTAL_LOG_DATA_SR      : NO PL/SQL procedure successfully completed. --//输出dbid开头的字段名以及包含supp的字段名。 --//如果有一些脚本使用参数1，参数2就不行了。例子如下： SCOTT@book01p> @ tpt/seg2 dept     SEG_MB OWNER                SEGMENT_NAME                   SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------          0 SCOTT                DEPT                           TABLE                USERS                                   8         12        130 SCOTT@book01p> @ prx '' '' PL/SQL procedure successfully completed. --//seg2.sql脚本参数1是dept。而prx执行时设置参数1='' --//继续执行： SCOTT@book01p> @ pr PL/SQL procedure successfully completed. --//因为参数1已经重置，没有输出。 SCOTT@book01p> @ pr dept ============================== SEG_MB                        : 0 SEG_OWNER                     : SCOTT SEG_SEGMENT_NAME              : DEPT SEG_PARTITION_NAME            : SEG_SEGMENT_TYPE              : TABLE SEG_TABLESPACE_NAME           : USERS BLOCKS                        : 8 HDRFIL                        : 12 HDRBLK                        : 130 PL/SQL procedure successfully completed. 3.补充说明： --//为什么在get与host两行之间最后存在1个空行。 --//测试遇到的问题，通过例子演示： SCOTT@book01p> select * from emp where mgr is null   2  ;      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10 --//注意分号在第2行。 SCOTT@book01p> @ prx 1,2,3 '' ============================== EMPNO                         : 7839 ENAME                         : KING JOB                           : PRESIDENT PL/SQL procedure successfully completed. --//第1次执行没有问题 SCOTT@book01p> @ prx 1,2,3 '' ORA-06512: at "SYS.DBMS_SQL", line 1244 ORA-06512: at line 13 declare * ERROR at line 1: ORA-00933: SQL command not properly ended ORA-06512: at line 36 ORA-06512: at "SYS.DBMS_SQL", line 1244 ORA-06512: at line 13 --//第2次执行没有问题. SCOTT@book01p>edit select * from emp where mgr is null host &_delete &_pr_tmpfile &_set_tmpfile / --//edit打开后看到的内容如上，明显执行错误。 --//在get与host两行之间最后存在1个空行，就不存在这个问题，建议pr系列的版本都在这两行之间加入1个空行。 SCOTT@book01p> select * from emp where mgr is null   2  ;      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10 SCOTT@book01p> @ prx 1,2,3 '' ============================== EMPNO                         : 7839 ENAME                         : KING JOB                           : PRESIDENT PL/SQL procedure successfully completed. SCOTT@book01p> @ prx 1,2,3 '' ============================== EMPNO                         : 7839 ENAME                         : KING JOB                           : PRESIDENT PL/SQL procedure successfully completed. --//还有1个问题就是我通过判断参数2是否存在逗号是否是数字顺序，如果仅仅输入1个数字没有逗号会存在问题，可以简单加入,0规避 --//这个问题或者输入一个很大的数字比如1001代替0.