139 lines
4.9 KiB
SQL
139 lines
4.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for objects
|
|
* 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)
|
|
* 5 - Object Status (% - wildchar, \ - escape char)
|
|
* 6 - Where Clause
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-JUN-13 Vishal Gupta Added object status as input parameter
|
|
* 20-Feb-13 Vishal Gupta Added SubObject name as input parameter
|
|
* 02-Jul-12 Vishal Gupta Modified to allow pass OWNER, OBJECT_NAME either as separate input
|
|
* or as single input joined as owner.object_name
|
|
* 05-Aug-04 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE subobject_name
|
|
UNDEFINE object_type
|
|
UNDEFINE object_status
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE subobject_name="&&3"
|
|
DEFINE object_type="&&4"
|
|
DEFINE object_status="&&5"
|
|
DEFINE WHERECLAUSE="&&6"
|
|
|
|
|
|
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 DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&object_name'),'','%','&&object_name') "_object_name"
|
|
, DECODE(UPPER('&&subobject_name'),'','%','&&subobject_name') "_subobject_name"
|
|
, DECODE(UPPER('&&object_type'),'','%','&&object_type') "_object_type"
|
|
, DECODE(UPPER('&&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"
|
|
/*, 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 * O B J E C 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 * - Object Status = '&&object_status'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
|
|
COLUMN owner FORMAT a20
|
|
COLUMN object_name FORMAT a30
|
|
COLUMN subobject_name FORMAT a30
|
|
COLUMN object_type FORMAT a20
|
|
COLUMN owner_object_name FORMAT a50
|
|
COLUMN last_ddl_time HEADING "LastDDLTime" FORMAT a18
|
|
COLUMN last_specification_change HEADING "Last|Specification|Change" FORMAT a18
|
|
COLUMN created HEADING "Created" FORMAT a18
|
|
|
|
|
|
SELECT *
|
|
FROM
|
|
( SELECT o.owner
|
|
, o.object_name
|
|
, o.subobject_name
|
|
, o.object_type
|
|
, o.status
|
|
, TO_CHAR(o.last_ddl_time,'DD-MON-YY HH24:MI:SS') last_ddl_time
|
|
, TO_CHAR(TO_DATE(o.timestamp,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') last_specification_change
|
|
, TO_CHAR(o.created,'DD-MON-YY HH24:MI:SS') created
|
|
FROM dba_objects o
|
|
WHERE o.owner like '&&owner' ESCAPE '\'
|
|
AND o.object_name like '&&object_name' ESCAPE '\'
|
|
AND NVL(o.subobject_name,'%') like '&&subobject_name' ESCAPE '\'
|
|
AND o.object_type like '&&object_type' ESCAPE '\'
|
|
AND o.status like '&&object_status' ESCAPE '\'
|
|
UNION ALL
|
|
SELECT 'SYS'
|
|
, f.name
|
|
, ' '
|
|
, f.type
|
|
, ' '
|
|
, ' '
|
|
, ' '
|
|
, ' '
|
|
FROM v$fixed_table f
|
|
WHERE 'SYS' like UPPER('&&owner') ESCAPE '\'
|
|
AND f.name like UPPER('&&object_name') ESCAPE '\'
|
|
AND f.type like UPPER('&&object_type') ESCAPE '\'
|
|
AND 'VALID' like '&&object_status' ESCAPE '\'
|
|
)
|
|
WHERE 1=1
|
|
&&WHERECLAUSE
|
|
ORDER BY owner, object_name ;
|
|
|
|
|
|
@@footer
|
|
|