CODEWITHSUNDEEP
sql-serverdatabaseoracle-databaseheterogeneousora-00942
d_S97
d_S97

Reputation: 21

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

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?

Upvotes: 0

Views: 2104

Answers (1)

Chuck
Chuck

Reputation: 1031

Try the openquery function, like this:

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

Upvotes: 0

Related Questions