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

Question

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.

pmdba · Accepted Answer · 2021-01-21T12:06:45.547

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.

score 2 · Answer 2 · answered Jan 20 '21 at 14:47

I'd suggest using synonyms, and set up a procedure to change them all at once, e.g.

create or replace procedure SET_DBLINK ( target_link IN VARCHAR2 ) is
begin
  execute immediate 'create or replace synonym TABLE1 for TABLE1@' || target_link;
  execute immediate 'create or replace synonym TABLE2 for TABLE2@' || target_link;
  -- ...etc... 
end;
/

That way you could do:

exec SET_DBLINK('dblink3');
select * from table1;

You'd probably want to add validation and exception handling to the procedure, but I left it simple for readability.

Thankyou! Interesting solution, I am still afraid how it will work in multithreading environment when several queries are retrieving info from different customers.. I'll will try solution and go back with experience. — Aleksandr Beliavski, Jan 25 '21 at 09:19

score 1 · Answer 3 · answered Jan 21 '21 at 06:51

For example, user manager

CREATE OR REPLACE VIEW manager.example1 (
   column1,
   column2
    )
AS
select 1, user from dual@dblink1
where 'SCOTT'=user
union all
select 2, user from dual@dblink2
where 'MANAGER'=user
union all
select 3, user from dual@dblink3
where 'HR'=user;


grant select on example1 to scott;
grant select on example1 to hr;

select * from example1;
==>
COLUMN1                                      COLUMN2                              
-------------------------------------------- ------------------------------------ 
                                           2 MANAGER

user scott

create synonym example1 for manager.example1;

select * from example1;
==>
COLUMN1                                      COLUMN2                              
-------------------------------------------- ------------------------------------ 
                                           1 SCOTT

user hr

create synonym example1 for manager.example1;

select * from example1;
==>
COLUMN1                                      COLUMN2                              
-------------------------------------------- ------------------------------------ 
                                           3 HR

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

3 Answers3