Toad v.16 error displaying synonyms using dg4msql gateway db link

I created a db link using Oracle dg4msql gateway to a database in SQL Server 2019.
The db link works fine. select from tables using db link is ok.
The test of db links in schema browser is ok.
The problem is after creating a synonym for an object using that db link.
A click in schema browser -->synonyms on the synonym gives error ora-00904 .
The synonym can be used in a select with no problem.
Have anybody encountered such a problem?

Strange... I'm guessing it might have to do with how Toad is attempting to grab the metadata for the synonym... Toad can show you what it's doing as your working in its interface, and that might help us with more info about what's causing the error.

Make sure you're listing the synonyms in your schema browser (but don't select the offending synonym just yet). Turn on spooling (see snap below), and then select the synonym and see if you can't locate the offending statement. If you want, post here, so we can see what's going on.

Here is the output of the spool:


-- Session: SYS@ORATST
-- Timestamp: 20:51:43.430
Select 1
from sys.DBA_SYNONYMS
where owner = :o
and synonym_name = :nm;
:o(VARCHAR[5],IN)='DOTAN'
:nm(VARCHAR[11],IN)='GW_DPT_1113'


-- Session: SYS@ORATST
-- Timestamp: 20:51:43.445
Select table_owner, table_name, db_link
from sys.DBA_SYNONYMS
where synonym_name = :obj
and owner = :own;
:obj(VARCHAR[11],IN)='GW_DPT_1113'
:own(VARCHAR[5],IN)='DOTAN'


-- Session: SYS@ORATST
-- Timestamp: 20:51:43.445
Select username from sys.all_db_links
where (upper(db_link) like 'DPT_SQL_TST.%' or upper(db_link) = 'DPT_SQL_TST');


-- Session: SYS@ORATST
-- Timestamp: 20:51:43.445
SELECT version
FROM SYS.PRODUCT_COMPONENT_VERSION@DPT_SQL_TST
WHERE UPPER(PRODUCT) LIKE '%ORACLE%';
-- ORA-00942: table or view does not exist
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'SYS.PRODUCT_COMPONENT_VERSION'. {42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these. {HY000,NativeErr = 11529}
ORA-02063: preceding 2 lines from DPT_SQL_TST


-- Session: SYS@ORATST
-- Timestamp: 20:51:44.352
Select owner, object_name, object_type
from sys.all_objects@DPT_SQL_TST
where object_name = 'DPT_1113'
and object_type in ('VIEW', 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'TABLE', 'SEQUENCE', 'SNAPSHOT', 'MATERIALIZED VIEW', 'OPERATOR', 'QUEUE', 'JAVA CLASS', 'JAVA SOURCE', 'SYNONYM', 'ANALYTIC VIEW', 'ATTRIBUTE DIMENSION', 'HIERARCHY', 'TYPE', 'UNDEFINED')
and owner in ('GTW_DPT_SQL_USER', 'PUBLIC')
order by 3;
-- ORA-00904: "OWNER": invalid identifier

The bug is that TOAD runs a select from the wrong database.
As it can be observed in the spooled code, the Oracle table SYS.PRODUCT_COMPONENT_VERSION is queried from the sql server db link.
This means that Toad assumes that the db link is defined to an Oracle database but it is NOT. It is defined to access an SQL Server database.

Could anyone pass this bug to Toad Development team?

Yes, I see it. Sorry for the delay. I was out for 2 weeks.

The version query is being handled internally and it seems like that is not the problem. The problem is that this basic data dictionary query (below) is throwing an error, because the OWNER field does not exist.

Select owner, object_name, object_type
from sys.all_objects@DPT_SQL_TST
where object_name = 'DPT_1113'
and object_type in ('VIEW', 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'TABLE', 'SEQUENCE', 'SNAPSHOT', 'MATERIALIZED VIEW', 'OPERATOR', 'QUEUE', 'JAVA CLASS', 'JAVA SOURCE', 'SYNONYM', 'ANALYTIC VIEW', 'ATTRIBUTE DIMENSION', 'HIERARCHY', 'TYPE', 'UNDEFINED')
and owner in ('GTW_DPT_SQL_USER', 'PUBLIC')
order by 3;

What column names do you get if you do a "select * from all_objects@DPT_SQL_TST"?

Unfortunately, I am guessing that if we get past this one problem, there is another lurking around the corner. But I'm willing to work around this if we can.

Oracle has a thing called "Transparent gateway" that I is supposed to make the data dictionary in the DB Link look like an Oracle data dictionary. I'm not sure if your link uses that or not. If not, then I suspect that will solve this.

The select * from "sys"."all_objects"@DPT_SQL_TST" gives all the column names in the following link.

sys.all_objects

There is no owner column.
There is a column type instead of object_type, name instead object_name.

I see. I'm afraid this isn't going to be supported any time soon. Toad for Oracle wasn't intended to support other database vendors. You might have better luck with Toad Data Point, which does support multiple types of databases.