Search code examples
oraclesessiondblink

Is it possible to set default ORACLE dblink for session and avoid @dblink_name suffixes

I have an Oracle database with a couple of public dblinks for various customers. All linked databases for all customers are identical. Also I have a schema on my host machine only, where I stored DB views with some data retrieval logic. Currently, if I want to retrieve the same data from various customers, I have to create separate almost identical views for every client:

CREATE VIEW my_view_for_cliet1 AS 
SELECT *
FROM table1@dblink1;

CREATE VIEW my_view_for_cliet2 AS 
SELECT *
FROM table1@dblink2

Is it possible to set default dblink for session (or something similar) and have only one DB view without explicit dblink, for example:

CREATE VIEW my_view AS 
    SELECT *
    FROM table1;

-- below I want to retrieve data from 3rd client 
ALTER SESSION SET DEFAULT DBLINK dblink3;
 
SELECT * FROM my_view;

P.S. I have only SELECT rights on linked machines so I can't create any views or other objects.

Solution

  • No, that is not possible. Every reference to a dblink must be explicit.

    Note that each of the other solutions presented (so far) can give the appearance of what you're asking, but still require explicit dblink references in all of the actual views and thus the same DDL changes for every new link and/or client user. There's no way to avoid creating those individual views or explicit references at some level (which is what I believe you were asking), even if you hide them somewhat from the user.