126 lines
5.1 KiB
SQL
126 lines
5.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display stored procedure arguments
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
|
|
* 2 - Object name (% - wildchar, \ - escape char)
|
|
* 3 - SubObject name (% - wildchar, \ - escape char)
|
|
* 4 - Object Type (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -------------------------------------------------------------------
|
|
* 12-Nov-15 Vishal Gupta Fixed issue where non-packaged proc/func arguments were not display
|
|
* because procedure_name is NULL in such cases
|
|
* 02-Jul-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE subobject_name
|
|
UNDEFINE object_type
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE subobject_name="&&3"
|
|
DEFINE object_type="&&4"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _subobject_name NEW_VALUE subobject_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT UPPER(DECODE('&&owner','','%','&&owner')) "_owner"
|
|
, UPPER(DECODE('&&object_name','','%','&&object_name')) "_object_name"
|
|
, UPPER(DECODE('&&subobject_name','','%','&&subobject_name')) "_subobject_name"
|
|
, UPPER(DECODE('&&object_type','','%','&&object_type')) "_object_type"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
END "_object_name"
|
|
/*, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
ELSE DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type'))
|
|
END "_object_type"
|
|
*/
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * P R O C E D U R E A R G U M E N T S L I S T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&object_name'
|
|
PROMPT * - SubObject Name = '&&subobject_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN subprogram_id noprint
|
|
COLUMN subobject_name FORMAT a56
|
|
COLUMN position FORMAT 999
|
|
COLUMN argument_name FORMAT a30
|
|
COLUMN sequence HEADING "Seq" FORMAT 99
|
|
COLUMN data_level HEADING "Lvl" FORMAT 99
|
|
COLUMN position HEADING "Pos" FORMAT 99
|
|
COLUMN defaulted HEADING "Default|ed" FORMAT a7
|
|
COLUMN data_type FORMAT a40
|
|
|
|
BREAK ON subprogram_id SKIP 1
|
|
|
|
SELECT p.owner || '.' || p.object_name || '.' || p.procedure_name subobject_name
|
|
, a.subprogram_id
|
|
, a.sequence
|
|
, a.data_level
|
|
, a.position
|
|
, a.in_out
|
|
, LPAD(' ',a.data_level * 2) || a.argument_name argument_name
|
|
, a.defaulted
|
|
, CASE
|
|
WHEN a.data_type = 'NUMBER'
|
|
THEN a.data_type || NVL2(a.data_precision,'(' || a.data_precision || ',' || a.data_scale || ')','')
|
|
WHEN a.data_type = 'FLOAT'
|
|
THEN a.data_type || NVL2(a.data_precision,'(' || a.data_precision || ')','')
|
|
WHEN a.data_type = 'VARCHAR2' OR a.data_type = 'CHAR' OR a.data_type = 'NVARCHAR2' OR a.data_type = 'NCHAR'
|
|
THEN a.data_type || NVL2(a.char_length,'(' || a.char_length || ' ' || DECODE(a.char_used,'B','BYTE','CHAR') || ')','')
|
|
WHEN a.data_type = 'RAW' OR a.data_type = 'UROWID'
|
|
THEN a.data_type || NVL2(a.data_length,'(' || a.data_length || ')' ,'')
|
|
WHEN a.type_OWNER IS NOT NULL
|
|
THEN a.data_type || ' (' || a.type_owner || '.' || a.type_name || NVL2(a.type_subname, '.' || a.type_subname, '') || ')'
|
|
ELSE a.data_type
|
|
END data_type
|
|
--, a.default_value
|
|
FROM dba_procedures p
|
|
LEFT OUTER JOIN dba_arguments a ON a.owner = p.owner AND a.object_id = p.object_id AND a.subprogram_id = p.subprogram_id
|
|
WHERE p.owner like UPPER('&&owner') ESCAPE '\'
|
|
AND p.object_name like UPPER('&&object_name') ESCAPE '\'
|
|
AND NVL(p.procedure_name,'x') like UPPER('&&subobject_name') ESCAPE '\'
|
|
AND p.object_type like UPPER('&&object_type') ESCAPE '\'
|
|
ORDER BY subobject_name
|
|
, a.subprogram_id
|
|
, a.sequence
|
|
;
|
|
|
|
|
|
@@footer
|