ORA-00942: table or view does not exist when Selecting something from a table from a heterogenous service

Question

I need to select table data in a Oracle Database from a Table, that is available in a SQL Server database. Therefore I created a database link to this Oracle database as described in http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm.

The database link is working correctly since I can show all tables from this db link with the command:

select * 
from all_tables@mssql_link

For instance I get a table with "adr" as table name and "dbo" as owner.

So if I try to select everything from this table via

select * from dbo.adr@mssql_link

or via

select * from "dbo"."adr"@mssql_link

or

select * from adr@mssql_link

I always get an Oracle exception:

ORA-00942: table or view does not exist
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DBO.ADR'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
ORA-02063: preceding 2 lines from MSSQL_LINK

I created the database link via create public database link mssql_link connect to "[dbUser]" identified by "[PASSWORD]" using 'mssql01';

Does anybody have an idea what is going on there?

I don't know about SQLServer, but if this had happened to an Oracle db, your user does not have select access to the table in question. Your link is set up to connect as a certain user. Does that user have appropriate access to the table in question. — unleashed

Chuck Chuck · Accepted Answer · 2017-04-11T17:05:28

Try the openquery function, like this:

select * from openquery(mssql_link, 'Select * from adr')

ORA-00942: table or view does not exist when Selecting something from a table from a heterogenous service

1 Answers