All geek questions in one place

Cannot query SQL Server system tables via db link created using DG4MSQL

I am trying to create a db link from Oracle 11g to SQL Server 2005 using the DG4MSQL gateway.

After creating the db link, I cannot query the SQL Server system views (sys.services or sys.objects) using the JDBC driver, but I can query all user tables using the JDBC driver.

If I use sqlplus, I can query all tables, including system tables. Since my project is a Java project, I must use the JDBC driver.

Another remark I made is that if I use DG4ODBC instead of the DG4MSQL gateway, then I can query all SQL Server tables, including system tables, using the JDBC driver.

Please let me know if there are any ways to query SQL Server system tables using the DG4MSQL and JDBC drivers?

+4
source share
1 answer

it's a little hard to explain

Oracle Gateway performs 3 types of operations:

  • SQL translations (when querying regular tables, views, etc.)
  • Data dictionary translations (when querying system views)
  • Data type conversions (e.g. Microsoft date for Oracle date)

In the case of JDBC, the JDBC-ODBC bridge makes the JDBC driver fully compatible with the drivers included in DG4ODBC. Therefore, JDBC plus DG4ODBC allows you to perform all of the above conversions.

The problem is that DG4MSQL uses a proprietary driver, and only SQL translations can be connected to JDBC.

As a solution to your problem, you can try to create local views in your Oracle schema based on the system views of remote SQL servers. Depending on your requirements, you can even create them as materialized views.

CREATE OR REPLACE VIEW sys_services AS SELECT * FROM sys.services@dblink ;

and then request sys_services instead of a direct request sys.services@dblink

0
source

Source: https://habr.com/ru/post/1387176/

More articles:


All Articles