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.
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.
It is definitely a Toad bug. It is not a question of supporting other databases.
Because there is an official Oracle feature called: oracle database gateway for sql server and other databases.
Oracle Transparent Gateway was the old name used till oracle gateway version 10gR2.
Oracle Database Gateway is the new name used from 11gR1 and higher.
This feature makes available data objects from other databases through a dblink.
In addition, having the need of data from some other tables that are actually in SQL Server, so why shouldn't Toad support this?
You can still select from tables through DB Links in the Editor, but the Schema Browser and other windows are designed to work with Oracle's data dictionary. The SQL Server ALL_OBJECTS view that you gave me a link to is vastly different from Oracle's ALL_OBJECTS view.
It is worth to add this capability to TOAD.
For example in Toad's dblink browser, you can add a feature by a right clik on the dblink name, and then set it as an sql server dblink (or other db).
In sql server case Toad will query from SQL Server ALL_OBJECTS instead of Oracle ALL_OBJECTS.