Concatenate bind variable in oracle

Question

I have a requirement where I have to select name of DB link (there are many DB links) from a table into bind variable and then fetch data from a table which is available in all DB links however data is different depending on DB link used. I am not getting a solution to use bind variables value as DB link.

This is my code:

select statement for fetching DB link into bind variable

SELECT DB_LINK into :v_db_link from reagions_db_links;

Then I have to use it for fetching data from table

SELECT reagion_id, region_name from Table_details@:v_db_link

I have tried to concatenate like below however its not working

SELECT reagion_id, region_name from Table_details@||:v_db_link

Please suggest me a solution, since I could have many DB links depending upon region selected by USER I am putting it into bind variable and then want to use it for fetching data from a table.

Nick Krasnov · Accepted Answer · 2012-11-20 14:19:12Z

Substitution variables can be used for that. here is a quick example of how it can be done: (Sql*plus environment).

-- set-up table that stores db_links
SQL> create table db_links(
  2    dblink_name varchar2(31)
  3  );

Table created.

--add a test dblink 
SQL> insert into db_links(dblink_name) values ('TEST_DB_LINK');

1 row created.

SQL> commit;

Commit complete.

-- defining of  a substitution variable dblink 
SQL> column dblink_name new_value dblink noprint;

-- the value of the dblink_name column will be placed into the dblink 
-- substitution variable declared previously
SQL> select dblink_name from db_links;

-- now we query a table using db link name stored
-- in the dblink substitution variable
-- prefacing it with ampersand.

SQL> select count(*) from dbusers@&dblink;
old   1: select count(*) from dbusers@&dblink
new   1: select count(*) from dbusers@TEST_DB_LINK

  COUNT(*)                                                                      
----------                                                                      
       351                                                                      

SQL> spool off;

Collectives™ on Stack Overflow

Concatenate bind variable in oracle

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related