[20160501]查看包参数脚本.txt
--我以前写的脚本
SCOTT@book> @ &r/desc_proc sys dbms_stats get_table%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE D
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- -
SYS DBMS_STATS GET_TABLE_STATS 1 OWNNAME VARCHAR2 IN VARCHAR2 N
1 OWNNAME VARCHAR2 IN VARCHAR2 N
2 TABNAME VARCHAR2 IN VARCHAR2 N
2 TABNAME VARCHAR2 IN VARCHAR2 N
3 PARTNAME VARCHAR2 IN VARCHAR2 Y
3 PARTNAME VARCHAR2 IN VARCHAR2 Y
4 STATTAB VARCHAR2 IN VARCHAR2 Y
4 STATTAB VARCHAR2 IN VARCHAR2 Y
5 STATID VARCHAR2 IN VARCHAR2 Y
5 STATID VARCHAR2 IN VARCHAR2 Y
6 NUMROWS NUMBER OUT NUMBER N
6 NUMROWS NUMBER OUT NUMBER N
7 NUMBLKS NUMBER OUT NUMBER N
7 NUMBLKS NUMBER OUT NUMBER N
8 AVGRLEN NUMBER OUT NUMBER N
8 AVGRLEN NUMBER OUT NUMBER N
9 STATOWN VARCHAR2 IN VARCHAR2 Y
9 STATOWN VARCHAR2 IN VARCHAR2 Y
10 CACHEDBLK NUMBER OUT NUMBER N
11 CACHEHIT NUMBER OUT NUMBER N
20 rows selected.
--很明显这样的输出参数排序了,不是自己需要的.删除排序操作看看:
SCOTT@book> @ &r/desc_proc sys dbms_stats get_table%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_STATS GET_TABLE_STATS 1 OWNNAME VARCHAR2 IN VARCHAR2 N
2 TABNAME VARCHAR2 IN VARCHAR2 N
3 PARTNAME VARCHAR2 IN VARCHAR2 Y
4 STATTAB VARCHAR2 IN VARCHAR2 Y
5 STATID VARCHAR2 IN VARCHAR2 Y
6 NUMROWS NUMBER OUT NUMBER N
7 NUMBLKS NUMBER OUT NUMBER N
8 AVGRLEN NUMBER OUT NUMBER N
9 STATOWN VARCHAR2 IN VARCHAR2 Y
1 OWNNAME VARCHAR2 IN VARCHAR2 N
2 TABNAME VARCHAR2 IN VARCHAR2 N
3 PARTNAME VARCHAR2 IN VARCHAR2 Y
4 STATTAB VARCHAR2 IN VARCHAR2 Y
5 STATID VARCHAR2 IN VARCHAR2 Y
6 NUMROWS NUMBER OUT NUMBER N
7 NUMBLKS NUMBER OUT NUMBER N
8 AVGRLEN NUMBER OUT NUMBER N
9 STATOWN VARCHAR2 IN VARCHAR2 Y
10 CACHEDBLK NUMBER OUT NUMBER N
11 CACHEHIT NUMBER OUT NUMBER N
$ cat desc_proc.sql
column owner format a10
column package_name format a20
column object_name format a30
column data_type format a20
column ARGUMENT_NAME format a20
column defaulted format a10
set verify off
break on owner on package_name on object_name skip 1
PROMPT INPUT OWNER PACKAGE_NAME OBJECT_NAME
PROMPT sample : @desc_proc sys dbms_stats gather_%_stats
Prompt
SELECT owner,
package_name,
object_name,
sequence,
argument_name,
data_type,
in_out,
data_type,
defaulted
FROM dba_arguments
WHERE owner = NVL(upper('&1'),'SYS')
AND package_name = NVL(upper('&2'),'DBMS_STATS')
AND object_name like NVL(upper('&3'),object_name)
AND data_level = 0
--ORDER BY owner,package_name,object_name,sequence;