KB442225: How to show oracle synonym in warehouse catalog

Symptom
By default, oracle synonym object does not show in the table list in Warehouse Catalog.

 
Synonym “TEST_274654_SYNONYM” is not showed in the “Table available” list on the left side in the Warehouse Catalog.
 
CAUSE:
Because the default SQL in “Catalog - Read Settings” only extract tables and views from tables “ALL_TABLES” and “ALL_VIEWS”, but the synonym info does not include in the two tables.
 
WORKAROUND:
Modify the default SQL in “Catalog - Read Settings” like below:

1. SQL for retrieving table list

SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME
FROM ALL_TABLES
WHERE OWNER = '#LOGIN_NAME#' AND DROPPED = 'NO'
UNION
SELECT DISTINCT OWNER NAME_SPACE, VIEW_NAME TAB_NAME
FROM ALL_VIEWS
WHERE OWNER = '#LOGIN_NAME#'
---Add below SQL
UNION
SELECT DISTINCT OWNER NAME_SPACE, synonym_name TAB_NAME
FROM ALL_SYNONYMS
WHERE OWNER = '#LOGIN_NAME#'

SELECT OWNER NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, DATA_LENGTH DATA_LEN, DATA_PRECISION DATA_PREC, DATA_SCALE DATA_SCALE 

FROM ALL_TAB_COLUMNS 

WHERE TABLE_NAME in (#TABLE_LIST#) 

---Modification begin

UNION

SELECT b.OWNER NAME_SPACE, b.synonym_name TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, DATA_LENGTH DATA_LEN, DATA_PRECISION DATA_PREC, DATA_SCALE DATA_SCALE 

FROM ALL_TAB_COLUMNS a

inner join ALL_SYNONYMS b 

  ON a.table_name = b.table_name

WHERE b.SYNONYM_NAME in (#TABLE_LIST#)

---Modification end

ORDER BY 1, 2

Result after modified the SQL: