Laurent Schneider

How do I select thru a database link, where the database link is not fixed?

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from LSC_T@:db_link where x>0;  
select x from LSC_T@:db_link where x>0;  
                    *
ERROR at line 1:
ORA-01729: database link name expected

My solution using dynamic SQL
Remote databases DB02

create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;

Remote databases DB03

create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;

Local database DB01

create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2) 
return lsc_t_c is  
  rc lsc_t_c;  
begin  
  execute immediate 
    'select lsc_t_o(x) from '
    || dbms_assert.qualified_sql_name(str) bulk collect into rc;  
  return rc;  
end;  
/

Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection

Ok, let’s try

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
  ||:db_link)) where x>0;  
         X
----------
         2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;  
         X
----------
         3

Seems to work