@@header
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Synonyms 
*  Parameters : 1 - owner         (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
*               2 - Synonymn name (% - wildchar, \ - escape char)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  18-SEP-14  Vishal Gupta  Appended db_link to table_name and remove separate output column for DB_LINK
*  11-Dec-04  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE synonym_name

DEFINE owner="&&1"
DEFINE synonym_name="&&2"


COLUMN  _owner            NEW_VALUE owner            NOPRINT
COLUMN  _synonym_name     NEW_VALUE synonym_name     NOPRINT

set term off

SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
     , DECODE(UPPER('&&synonym_name'),'','%','&&synonym_name') "_synonym_name"
FROM DUAL;

SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
     , CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) 
           ELSE DECODE(UPPER('&&synonym_name'),'','%',UPPER('&&synonym_name')) 
       END    "_synonym_name"
FROM DUAL;
set term on


PROMPT *****************************************************************
PROMPT * S Y N O N Y M N     L I S T  
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - Owner       = '&&owner'
PROMPT *  - Synonym Name = '&&synonym_name'
PROMPT *****************************************************************

COLUMN synonym_name              FORMAT a50
COLUMN table_name                FORMAT a50
COLUMN last_ddl_time             HEADING "LastDDLTime"                     FORMAT a18
COLUMN created                   HEADING "Created"                         FORMAT a18


SELECT s.owner || '.' || s.synonym_name synonym_name
     , s.table_owner || '.' || s.table_name || NVL2(s.db_link,'@'||s.db_link,'') table_name
     , TO_CHAR(o.last_ddl_time,'DD-MON-YY HH24:MI:SS') last_ddl_time
     , TO_CHAR(o.created,'DD-MON-YY HH24:MI:SS')  created
     --, s.db_link
   FROM dba_synonyms s
      , dba_objects  o
   WHERE s.owner        = o.owner
     AND s.synonym_name = o.object_name
     AND o.object_type = 'SYNONYM'
     AND o.owner       like '&&owner'        ESCAPE '\'
     AND s.synonym_name like '&&synonym_name'  ESCAPE '\'
ORDER BY s.synonym_name  
       , s.owner  
;


@@footer