[20140820]顯示儲存過程的引數.txt

[20140820]顯示儲存過程的引數.txt

--上午寫一個顯示儲存過程引數的例子，便於自己以後使用。
--主要是desc顯示的資訊太大，檢視不是很方便。

SCOTT@test> desc dbms_stats
PROCEDURE ALTER_DATABASE_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
MONITORING                     BOOLEAN                 IN     DEFAULT
SYSOBJS                        BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_SCHEMA_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN     DEFAULT
MONITORING                     BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_STATS_HISTORY_RETENTION
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETENTION                      NUMBER                  IN
....

PROCEDURE UPGRADE_STAT_TABLE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
STATTAB                        VARCHAR2                IN

SCOTT@test> desc dbms_stats.gather_table_stats
ERROR:
ORA-04043: object dbms_stats.gather_table_stats does not exist
--如果支援這種顯示方式就好了。

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

SCOTT@test> @desc_proc sys dbms_stats gather_s%_stats
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           GATHER_SCHEMA_STATS                     1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 OBJLIST              TABLE                OUT       TABLE                N
                                                                       12 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       13 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       14 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       15 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       16 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       17 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y
                                                                       18 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       19 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       20 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       21 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       22 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       23 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       24 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y

                                GATHER_SYSTEM_STATS                     1 GATHERING_MODE       VARCHAR2             IN        VARCHAR2             Y
                                                                        2 INTERVAL             NUMBER               IN        NUMBER               Y
                                                                        3 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATOWN              VARCHAR2             IN        VARCHAR2             Y

40 rows selected.

SCOTT@test> @desc_proc sys dbms_metadata get_ddl
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_METADATA        GET_DDL                                 1                      CLOB                 OUT       CLOB                 N
                                                                        2 OBJECT_TYPE          VARCHAR2             IN        VARCHAR2             N
                                                                        3 NAME                 VARCHAR2             IN        VARCHAR2             N
                                                                        4 SCHEMA               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 VERSION              VARCHAR2             IN        VARCHAR2             Y
                                                                        6 MODEL                VARCHAR2             IN        VARCHAR2             Y
                                                                        7 TRANSFORM            VARCHAR2             IN        VARCHAR2             Y

7 rows selected.