dynamic database link

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 🙂

10 thoughts on “dynamic database link”

  1. coskan

    Laurent,

    you make the dynamic link but you are fixing the table and column in function ????

    I think creating a procedure and passing table names and db_link to that procedure and calling execute immediate after generating the dynamic sql isnt it a bit more flexible ???

    using a type looks more harder to cope when you want to add columns (ORA-02303: cannot drop or replace a type with type or table dependents)

  2. Laurent Schneider Post author

    I should have given more details, the idea about using a type is that you can use the column name in the query (where x<0). In my current project we have different sources (different db links), but the table structure is identical. So we always want to SELECT X, whatever the destination is. Currently, we do use a plain execute immediate approach in plsql instead of a sql approach using table function, but I think that returning a type with a defined number of attributes (columns) could be interesting…

    Thanks for your comment

  3. Pingback: Pythian Group - Blog

  4. APC

    Did you try it with

    select x from LSC_T@&db_link where x>0;

    ?

    It’s been a while since I’ve used this, and I can’t remember whether we need to double the @ (i.e. @@&db_link). I currently don’t have access to two databases to try it for myself.

    Cheers, APC

  6. APC

    Well your examples used and your post didn’t specify it had to be PL/SQL. Remember what we tell the newbies in the forums: please post ALL the relevant information 😉

    Cheers, APC

  10. Laurent Schneider Post author

    here it is!

    SQL> create database link l using 'DB01';

    Database link created.

    SQL> create procedure p is begin null; end;
    2 /

    Procedure created.

    SQL> declare link varchar2(30) := 'L';
    2 begin execute immediate 'begin p@'||link||';end;';
    3 end;
    4 /

    PL/SQL procedure successfully completed.

Comments are closed.