CODEWITHSUNDEEP
oracle-databasejdbcoracle11gdblink
hnm
hnm

Reputation: 829

cannot query SQL Server system tables over db link created using DG4MSQL

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

After creating db link I am not able to query SQL Server system views (sys.services or sys.objects) using JDBC driver, but I am able to query all user tables using JDBC driver.

If I use sqlplus, I am able to query all tables including system tables. Since my project is Java project, I am bound to use JDBC driver.

One more observation I made is that, if I use DG4ODBC instead of DG4MSQL gateway, then I am able to query all SQL Server tables including system tables using JDBC driver.

Please let me know if there are any ways I can query SQL Server system tables using DG4MSQL and JDBC driver?

Upvotes: 4

Views: 1454

Answers (1)

xionutz2k
xionutz2k

Reputation: 729

this one is a little bit tricky to explain

An Oracle Gateway performs 3 types of operations:

  • SQL translations (when you query regular tables, views etc)
  • Data Dictionary translations (when you query system views)
  • Data Type transformations (for example Microsoft's date to Oracle's date)

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

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

As a solution to your issue, you could try to create local views on your Oracle schema, based on the remote SQL server system views. 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 query sys_services instead of directly querying sys.services@dblink

Upvotes: 1

Related Questions