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

Published by

Laurent Schneider

Oracle Certified Master

10 thoughts on “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