@@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