96 lines
3.1 KiB
SQL
96 lines
3.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display object errors
|
|
* Parameters : 1 - Owner (% - wildcard, \ - escape char)
|
|
* 2 - Object Name (% - wildcard, \ - escape char)
|
|
* 3 - Object Type (% - wildcard, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 19-MAY-13 Vishal Gupta Added input parameters
|
|
* 05-Aug-04 Vishal Gupta Created
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE object_type="&&3"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&object_name'),'','%','&&object_name') "_object_name"
|
|
, DECODE(UPPER('&&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"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * S O U R C E C O D E E R R O R S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&object_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT *****************************************************************
|
|
|
|
COLUMN object HEADING "Object" FORMAT a40
|
|
COLUMN owner HEADING "Owner" FORMAT a20
|
|
COLUMN name HEADING "Name" FORMAT a30
|
|
COLUMN type HEADING "Type" FORMAT a12
|
|
COLUMN sequence HEADING "Seq" FORMAT 999
|
|
COLUMN line HEADING "Line" FORMAT 9990
|
|
COLUMN position HEADING "Col" FORMAT 999
|
|
COLUMN text HEADING "Error Text" FORMAT a100
|
|
COLUMN created HEADING "Create Time" FORMAT a20
|
|
|
|
--BREAK ON owner NODUPLICATES ON name NODUPLICATES ON type NODUPLICATES SKIP 1
|
|
BREAK ON object NODUPLICATES ON type NODUPLICATES SKIP 1
|
|
|
|
SELECT e.owner || '.' || e.name object
|
|
, e.type
|
|
, e.sequence
|
|
, e.line
|
|
, e.position
|
|
, e.text
|
|
FROM dba_errors e
|
|
WHERE e.owner LIKE UPPER('&owner') ESCAPE '\'
|
|
AND e.name LIKE UPPER('&object_name') ESCAPE '\'
|
|
AND e.type LIKE UPPER('&object_type') ESCAPE '\'
|
|
AND e.name NOT LIKE 'BIN$%'
|
|
ORDER BY e.owner
|
|
, e.name
|
|
, e.sequence
|
|
, e.line
|
|
, e.position
|
|
/
|
|
|
|
|
|
@@footer
|