[20260607]建立完善通用的prz.sql脚本.txt
[20260607]建立完善通用的prz.sql脚本.txt
--//自己写一个比较通用的版本prx.sql,参考链接 : [20251014]建立完善通用的prx.sql脚本.txt
--//参数1支持2种格式，第1种格式使用数字序列使用,分开,输出对应字段。第2种格式使用正则表达式输出对应字段.
--//参数2支持参数2|n，2表示使用dbms_sql.desc_tab2。n 输出带字段的顺序号.
--//如果有一些脚本使用参数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=''
--//为此另外写一个新版本prz.sql完善prx.sql的缺陷.实际上很简单就是使用_tpt_1,_tpt_2变量接收原来prx.sql的参数1,参数2.
--//源代码如下:
$ cat -v prz.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
col tpt_1 new_value _tpt_1
col tpt_2 new_value _tpt_2
SELECT NULL "1", NULL "2" , NULl tpt_1 , NULL tpt_2 FROM dual WHERE 1 = 2;
SELECT CASE WHEN INSTR (LOWER ('&_tpt_2'), '2') > 0 THEN '--' ELSE ' ' END tpt_pr
,CASE WHEN INSTR (LOWER ('&_tpt_2'), '2') > 0 THEN ' ' ELSE '--' END tpt_pr2
,CASE WHEN INSTR (LOWER ('&_tpt_2'), 'n') > 0 THEN '--' ELSE ' ' END tpt_prnn
,CASE WHEN INSTR (LOWER ('&_tpt_2'), 'n') > 0 THEN ' ' ELSE '--' END tpt_prn
,CASE WHEN INSTR (LOWER ('&_tpt_1'), ',') > 0 THEN ' ' WHEN '&_tpt_1' IS NULL THEN '--' ELSE '--' END tpt_pri
,CASE WHEN INSTR (LOWER ('&_tpt_1'), ',') > 0 THEN '--' WHEN '&_tpt_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('&_tpt_1')) then
999999 &_tpt_pri if i in (&_tpt_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
--//^F在linux下使用vim在输入状态按ctrl+v ctrl+f输入,windows在输入状态按ctrl+q ctrl+f输入。
--//简单测试:
SCOTT@book01p> @ tpt/seg2 dept p
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ ------------------- ------ ------ ------
0 SCOTT DEPT TABLE USERS 8 12 130
SCOTT@book01p> @ prx seg n
PL/SQL procedure successfully completed.
--//执行的sql语句含有&1，&2。相当于执行@ tpt/seg2 seg n 对应的sql语句，不存在seg的段名，自然没有输出。
--//通过给_tpt_1,_tpt_2赋值代替参数1，2来规避这个问题。
SCOTT@book01p> define _tpt_2=n
SCOTT@book01p> define _tpt_1=seg
SCOTT@book01p> @ tpt/seg2 dept p
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ ------------------- ------ ------ ------
0 SCOTT DEPT TABLE USERS 8 12 130
SCOTT@book01p> @ prz
==============================
001 SEG_MB : 0
002 SEG_OWNER : SCOTT
003 SEG_SEGMENT_NAME : DEPT
004 SEG_PARTITION_NAME :
005 SEG_SEGMENT_TYPE : TABLE
006 SEG_TABLESPACE_NAME : USERS
PL/SQL procedure successfully completed.
--//还可以利用#在执行时设置,输出包含e字符的字段名,例子如下:
SCOTT@book01p> select * from emp where sal>=3400;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=e
2 # define _tpt_2=n
2 @ prz 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
005 HIREDATE : 1981-11-17 00:00:00
008 DEPTNO : 10
PL/SQL procedure successfully completed.
--//注：必须在prz后面输入参数1，否则报错。
--//输出字段顺序号为1,2,3,5的字段名,
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=1,2,3,5,6
2 # define _tpt_2=n
2 @ prz 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
003 JOB : PRESIDENT
005 HIREDATE : 1981-11-17 00:00:00
006 SAL : 5000
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from emp where sal>=&2
2 # define _tpt_1=*
2 # define _tpt_2=n
2 @ prz '' 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
003 JOB : PRESIDENT
004 MGR :
005 HIREDATE : 1981-11-17 00:00:00
006 SAL : 5000
007 COMM :
008 DEPTNO : 10
PL/SQL procedure successfully completed.
--//缺点是必须在prz后面跟上参数，不然报错。
SCOTT@book01p> undefine 1 2
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=1,2,3,6
2 @ prz
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 12
declare
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at line 35
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 12
--//个人喜欢修改tpt的init.sql文件，加入_tpt_1,_tpt_2的缺省值定义：
define _tpt_1=*
define _tpt_2=2n
--//再举一个例子：
SCOTT@book01p> select * from v$database
2 # define _tpt_1=dbid|supp
2 # define _tpt_2=2n
2 @ prz
==============================
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
057 CON_DBID : 1073777561
059 SUPPLEMENTAL_LOG_DATA_SR : NO
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from v$database
2 @ prx dbid|supp 2n
==============================
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
057 CON_DBID : 1073777561
059 SUPPLEMENTAL_LOG_DATA_SR : NO
PL/SQL procedure successfully completed.
--//自己写一个比较通用的版本prx.sql,参考链接 : [20251014]建立完善通用的prx.sql脚本.txt
--//参数1支持2种格式，第1种格式使用数字序列使用,分开,输出对应字段。第2种格式使用正则表达式输出对应字段.
--//参数2支持参数2|n，2表示使用dbms_sql.desc_tab2。n 输出带字段的顺序号.
--//如果有一些脚本使用参数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=''
--//为此另外写一个新版本prz.sql完善prx.sql的缺陷.实际上很简单就是使用_tpt_1,_tpt_2变量接收原来prx.sql的参数1,参数2.
--//源代码如下:
$ cat -v prz.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
col tpt_1 new_value _tpt_1
col tpt_2 new_value _tpt_2
SELECT NULL "1", NULL "2" , NULl tpt_1 , NULL tpt_2 FROM dual WHERE 1 = 2;
SELECT CASE WHEN INSTR (LOWER ('&_tpt_2'), '2') > 0 THEN '--' ELSE ' ' END tpt_pr
,CASE WHEN INSTR (LOWER ('&_tpt_2'), '2') > 0 THEN ' ' ELSE '--' END tpt_pr2
,CASE WHEN INSTR (LOWER ('&_tpt_2'), 'n') > 0 THEN '--' ELSE ' ' END tpt_prnn
,CASE WHEN INSTR (LOWER ('&_tpt_2'), 'n') > 0 THEN ' ' ELSE '--' END tpt_prn
,CASE WHEN INSTR (LOWER ('&_tpt_1'), ',') > 0 THEN ' ' WHEN '&_tpt_1' IS NULL THEN '--' ELSE '--' END tpt_pri
,CASE WHEN INSTR (LOWER ('&_tpt_1'), ',') > 0 THEN '--' WHEN '&_tpt_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('&_tpt_1')) then
999999 &_tpt_pri if i in (&_tpt_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
--//^F在linux下使用vim在输入状态按ctrl+v ctrl+f输入,windows在输入状态按ctrl+q ctrl+f输入。
--//简单测试:
SCOTT@book01p> @ tpt/seg2 dept p
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ ------------------- ------ ------ ------
0 SCOTT DEPT TABLE USERS 8 12 130
SCOTT@book01p> @ prx seg n
PL/SQL procedure successfully completed.
--//执行的sql语句含有&1，&2。相当于执行@ tpt/seg2 seg n 对应的sql语句，不存在seg的段名，自然没有输出。
--//通过给_tpt_1,_tpt_2赋值代替参数1，2来规避这个问题。
SCOTT@book01p> define _tpt_2=n
SCOTT@book01p> define _tpt_1=seg
SCOTT@book01p> @ tpt/seg2 dept p
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ ------------------- ------ ------ ------
0 SCOTT DEPT TABLE USERS 8 12 130
SCOTT@book01p> @ prz
==============================
001 SEG_MB : 0
002 SEG_OWNER : SCOTT
003 SEG_SEGMENT_NAME : DEPT
004 SEG_PARTITION_NAME :
005 SEG_SEGMENT_TYPE : TABLE
006 SEG_TABLESPACE_NAME : USERS
PL/SQL procedure successfully completed.
--//还可以利用#在执行时设置,输出包含e字符的字段名,例子如下:
SCOTT@book01p> select * from emp where sal>=3400;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=e
2 # define _tpt_2=n
2 @ prz 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
005 HIREDATE : 1981-11-17 00:00:00
008 DEPTNO : 10
PL/SQL procedure successfully completed.
--//注：必须在prz后面输入参数1，否则报错。
--//输出字段顺序号为1,2,3,5的字段名,
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=1,2,3,5,6
2 # define _tpt_2=n
2 @ prz 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
003 JOB : PRESIDENT
005 HIREDATE : 1981-11-17 00:00:00
006 SAL : 5000
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from emp where sal>=&2
2 # define _tpt_1=*
2 # define _tpt_2=n
2 @ prz '' 3400
==============================
001 EMPNO : 7839
002 ENAME : KING
003 JOB : PRESIDENT
004 MGR :
005 HIREDATE : 1981-11-17 00:00:00
006 SAL : 5000
007 COMM :
008 DEPTNO : 10
PL/SQL procedure successfully completed.
--//缺点是必须在prz后面跟上参数，不然报错。
SCOTT@book01p> undefine 1 2
SCOTT@book01p> select * from emp where sal>=&1
2 # define _tpt_1=1,2,3,6
2 @ prz
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 12
declare
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at line 35
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 12
--//个人喜欢修改tpt的init.sql文件，加入_tpt_1,_tpt_2的缺省值定义：
define _tpt_1=*
define _tpt_2=2n
--//再举一个例子：
SCOTT@book01p> select * from v$database
2 # define _tpt_1=dbid|supp
2 # define _tpt_2=2n
2 @ prz
==============================
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
057 CON_DBID : 1073777561
059 SUPPLEMENTAL_LOG_DATA_SR : NO
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from v$database
2 @ prx dbid|supp 2n
==============================
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
057 CON_DBID : 1073777561
059 SUPPLEMENTAL_LOG_DATA_SR : NO
PL/SQL procedure successfully completed.
浙公网安备 33010602011771号