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