CODEWITHSUNDEEP
oracle-database
Shane
Shane

Reputation: 2629

Possible to link to another database link?

We have an existing database link in an Oracle database that links to data in a Sql Server database. Now, a 2nd Oracle database needs to use that same data. Due to security setup, the 2nd Oracle database cannot "see" the Sql Server database, but it can see the 1st Oracle database.

If we create a database link in the 2nd Oracle database that points to the 1st Oracle database, will we be able to query data from the Sql Server database in the 2nd Oracle database by going through 2 database links? Would the query syntax look like this:

SELECT * FROM myTable@2ndLink@1stLink

Has anyone done something like this before?

Upvotes: 1

Views: 4077

Answers (2)

dpbradley
dpbradley

Reputation: 11915

Vincent's solution will work, and another solution is to create a synonym instead of a view.

DB1:
CREATE SYNONYM X FOR MyTable@sqlServerDB

DB2:
(assumes db link to DB1 connects as owner of synonym)
SELECT * from X@DB1

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

I'm not sure this synthax would work (although it would be interesting to test it I can not do it right now). However, even if it doesn't work, you can still create a view in Database 1 that points to a table in your SQL Server Database. From Database 2, you could then query:

SELECT * FROM myView@db1

That would point to the correct table.

Upvotes: 2

Related Questions