Fetching DatabaseMetadata.getColumns When Object Is A Synonym (Doc ID 2690139.1)

Last updated on JUNE 29, 2023

Applies to:

Goal

The following scenarios show when the JDBC driver does and does not return column metadata for a synonym.

Scenario One:
Synonym C points to a table. Synonym B points to Synonym C. Synonym A points to Synonym B.
If you ask column metadata for A and B nothing is returned.
only when you ask for metadata on C will metadata be returned i.e.,
SYNA -> SYNB -> SYNC -> TABLE will only return column metadata for SYNC

Scenario Two:
User B defines a synonym to a table owned by User A.
User A defines a synonym to the synonym created by user B.
SCHMEAONE.SYN -> SCHEMATWO.SYN -> SCHEMAONE.TABLE i.e.,
the output is returned only when we fetch column metadata on SCHEMATWO.SYN.

Scenario Three:
A Database link from Database1:Schema1 to Database2:Schema2 is created.
SYNONYM1 in Database1 is pointing to TABLE1 in Database" (using remote database link).
When Connected as Schema1 on Database1, even though there is access to tables on Database2:Schema2, no metadata from Table1, Table2, Table3 in Schema2 on Database2 can be obtained when using getColumns(null,"Schema2","Table1",null),getColumns(null,"Schema2","Table2",null) and getColumns(null,"Schema2","Table3",null) respectively.

Is this expected behaviour ?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.