ansaurus

Question

Possible to link to another database link?

Answer 1

+2 A:

Hi Shane,

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.

Vincent Malgrat 2010-01-19 16:25:33

I am stepping through this and hit an unexpected error. I created a view for myTable@1stLink in DB1 and I granted SELECT on that view to a different user in DB1. Tried to SELECT from the view as User2 in DB1 and I get a username/password error. I think the DB Link was created as CURRENT_USER and only that single user in DB1 has a valid username/password on the Sql Server database. It will be a pain to get that user created anywhere else. Any idea how to work around this?

Shane 2010-01-19 20:06:34

@Shane: does the view work when you query it with the user that owns the view ?

Vincent Malgrat 2010-01-20 08:44:00

I was able to create a new public database link that has the user/password in it. That link and views created against it are working for all users. Thanks for the advice.

Shane 2010-01-20 13:51:51

Answer 2

+3 A:

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

dpbradley 2010-01-19 16:44:43

+1: synonyms will be easier to maintain than views (e.g. in case the source table adds/rename a column for example)

Vincent Malgrat 2010-01-19 16:54:57

ansaurus

tags:

views:

answers:

Possible to link to another database link?

related questions