[20160501]檢視包引數指令碼.txt

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