DBLINK Name as a variable oracle

0
votes

I had a problem while using dblink in oracle apex. I Have Created 3 Dblinks to 3 different remote database,DBLINKS

Now I want the below LOV's to be cascaded from the dblinks. So What should the write in the query as the name of dblink is a variable.Query for LOV

Thanks.

2
oracleoracle-apexdblink
YOu can't do that. In pl/sql you can use execute immediate, but I don't know how that works with apex. – OldProgrammer
I tried execute immediate but also generated error, is there any way where I can put variable in after "SELECT * FROM dba_roles@xxxx" in the xxx position? – Simanta
What error did it generate? Please post the code so we may assist effectively. Please post all relevant info when posting questions. – TenG
You can, but that would create a security concern. I'd go with LittleFoot's suggestion – Scott

2 Answers

1
votes

Suppose that the item you use to select a database is named P1_DATABASE and its source is

select database_name d, database_id r
from list_of_databases
order by database_id;

and returns

DBLINK1, 1
DBLINK2, 2
DBLINK3, 3

as display/return values.

Create a LoV that utilizes UNION, such as

select role d, role r 
  from dba_roles@dblink1
  where :P1_DATABASE = 1
union all
select role d, role r 
  from dba_roles@dblink2
  where :P1_DATABASE = 2
union all
select role d, role r 
  from dba_roles@dblink3
  where :P1_DATABASE = 3;

which means: if you select dblink1, its return value is 1 so the first SELECT will return some values, while 2nd and 3rd won't. The same goes for other options you choose.

That's the general idea; modify it if necessary.

0
votes

Use "PL/SQL Function Body returning SQL Query" then in the plsql you can build up the specific one you need based on inputs.

Along the lines of this>

declare
  v_sql varchar2(2000) := '';
begin
  if ( :P1_DATABASE = 1 ) then
    v_sql := 'select blah from blah@db1';
  elsif ( :P1_DATABASE = 2 ) then
    v_sql := 'select blah from blah@db2';
  elsif ( :P1_DATABASE = 3 ) then
    v_sql := 'select blah from blah@db3';
  end if;

  return v_sql;
end;

enter image description here