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