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);

Remote databases DB03

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

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;  
  execute immediate 
    'select lsc_t_o(x) from '
    || dbms_assert.qualified_sql_name(str) bulk collect into rc;  
  return rc;  

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;  
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;  

Seems to work 🙂

10 Replies to “dynamic database link”

  1. 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. 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. 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

  4. 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

  5. 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.

Leave a Reply

Your email address will not be published.