Hi Tom!
I need to create a view by using a dynamic database link.
I am working in a database with only one Oracle-user, PWPROD. This database is managed from another program called ProjectWise. In this program I have several users. I know the name of the current user of this program (called PWUSER), and now I need to connect to another database (called VDS.WORLD), where this user IS created and are granted different roles. I want to do this by using a dynamic database link so that the PWUSER will only get access to the tables and so which are granted to him in the other database. I try to describe what I need by the following description of tables, database links, function and the "desired view". Hope it makes sense.
create table userlinks (pwuser varchar2(30), dblinkname varchar2(30));
insert into userlinks values ('KFC','KFC_LINK');
insert into userlinks values ('JSM','JSM_LINK');create table current_user (pwuser varchar2(30));
insert into current_user values ('KFC');CREATE DATABASE LINK "KFC_LINK.WORLD" CONNECT TO KFC
IDENTIFIED BY kfcpassword USING 'VDS.WORLD';
CREATE DATABASE LINK "JSM_LINK.WORLD" CONNECT TO JSM
IDENTIFIED BY jsmpassword USING 'VDS.WORLD';
create or replace function current_user_fn
return
varchar2 is current_dblink varchar2(30);
begin
select dblinkname into current_dblink from userlinks where username = (select pwuser from current_user);
return current_dblink;
end;
And now, the view I desire:
create view ABC_LOCAL as
select * from ABC@call(current_user_fn);
I know that this doesn't work, by hope it decribes my needs?
My wish is, that when the current PWUSER is KFC, then the view is based on
create view ABC_LOCAL as
select * from ABC@KFC_LINK;
and when the PWUSER is JSM, then the view is based on
create view ABC_LOCAL as
select * from ABC@JSM_LINK;
sounds like you want a SINGLE database link and that database link would be a current_user link and you would use real users in the database.
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm#SQLRF01205 that is - KFC would log into the database as KFC so that "select user from dual" would return KFC. Likewise JSM would log in as JSM and select user from dual would return JSM for them.
otherwise, you will have to use dynamic sql with different database link names in the dynamically executed sql. There is no such capability as you outline above.