111 lines
4.0 KiB
SQL
111 lines
4.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for stored procedures/packages/functions/types etc.
|
|
* 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
|
|
* --------- ------------ ----------------------------------------------
|
|
* 13-JUN-14 Vishal Gupta Increased AUTHID column width
|
|
* 22-JAN-14 Vishal Gupta Updated column headings and rearranged columns
|
|
* 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
|
|
COLUMN _object_status NEW_VALUE object_status 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"
|
|
, UPPER(DECODE('&&object_status','','%','&&object_status')) "_object_status"
|
|
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"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * P R O C E D U R E 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 object_name HEADING "ObjectName" FORMAT a40
|
|
COLUMN subobject_name HEADING "SubObjectName" FORMAT a30
|
|
COLUMN object_type HEADING "ObjectType" FORMAT a15
|
|
COLUMN authid HEADING "Auth ID" FORMAT a12
|
|
COLUMN overload HEADING "Over|load" FORMAT a4
|
|
COLUMN pipelined HEADING "Pipe|lined" FORMAT a5
|
|
COLUMN aggregate HEADING "Aggr" FORMAT a4
|
|
COLUMN parallel HEADING "Par'l" FORMAT a5
|
|
COLUMN interface HEADING "Inter|face" FORMAT a5
|
|
COLUMN deterministic HEADING "Deter|minis|tic" FORMAT a6
|
|
|
|
|
|
SELECT p.owner || '.' || p.object_name object_name
|
|
, p.procedure_name subobject_name
|
|
, p.object_type
|
|
, p.authid
|
|
, p.overload
|
|
, p.pipelined
|
|
, p.aggregate
|
|
, p.parallel
|
|
, p.interface
|
|
, p.deterministic
|
|
FROM dba_procedures p
|
|
WHERE p.owner like UPPER('&&owner') ESCAPE '\'
|
|
AND p.object_name like UPPER('&&object_name') ESCAPE '\'
|
|
AND p.procedure_name like UPPER('&&subobject_name') ESCAPE '\'
|
|
AND p.object_type like UPPER('&&object_type') ESCAPE '\'
|
|
ORDER BY object_name
|
|
, p.procedure_name
|
|
;
|
|
|
|
|
|
@@footer
|