proc_defs.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : proc_defs.sql
--* Author       : DR Timothy S Hall
--* Description  : Lists the parameters for the specified package and procedure.
--* Call Syntax  : @proc_defs (package-name) (procedure-name or all)
--* Last Modified: 24/09/2003
COLUMN "Object Name" FORMAT A30
COLUMN ol FORMAT A2
COLUMN sq FORMAT 99
COLUMN "Argument Name" FORMAT A32
COLUMN "Type" FORMAT A15
COLUMN "Size" FORMAT A6
BREAK ON ol SKIP 2
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

SELECT object_name AS "Object Name",
       overload AS ol,
       sequence AS sq,
       RPAD(' ', data_level*2, ' ') || argument_name AS "Argument Name",
       data_type AS "Type",
       (CASE
         WHEN data_type IN ('VARCHAR2','CHAR') THEN TO_CHAR(data_length)
         WHEN data_scale IS NULL OR data_scale = 0 THEN TO_CHAR(data_precision)
         ELSE TO_CHAR(data_precision) || ',' || TO_CHAR(data_scale)
       END) "Size",
       in_out AS "In/Out",
       default_value
FROM   user_arguments
WHERE  package_name = UPPER('&1')
AND    object_name  = DECODE(UPPER('&2'), 'ALL', object_name, UPPER('&2'))
ORDER BY object_name, overload, sequence;

SET PAGESIZE 14
SET LINESIZE 80