title: How do I pass a database link as a variable in a stored procedure
body: <p>I have a form on a table. Entries in the form will define the link to use for a query. To make it work, I need to create a query that will select against the table and thereby generate the link to use. This is not the SQL I will be using, but the principle is the same. I have tried all manner of bars and quotes but I can't get oracle to compile the procedure and use the string in the table to action the query over the link. Can you help?</p>
  
  <pre><code>--Insert the name of a db_link here, in this case
  --All that the table contains is "DEV01"
  
  create table LINK_NAME
  (LINK_NAME VARCHAR2(20));
  
  --Now create a procedure to test selecting from the link
  --Just using the string in the table to get its name:
  
  create or replace procedure TEST_LINK is
  v_link_name varchar2(10);
  v_blah varchar2(10);
  v_link varchar2(10);
  
  --This passes the string "DEV01" into variable v_link:
  cursor c1 is select link_name into v_link from link_name;
  
  --This works, directly referencing the link name:
  --cursor c2 is select name into v_blah from v$database@DEV01;
  
  --This doesn't work, when I reference the link using the variable I've passed.
  cursor c2 is select name into v_blah from v$database@||v_link;
  --How can I get oracle to accept the variable to define the name of the link?
  
  begin
  open c1;
  loop
  fetch c1 into v_link;
  EXIT WHEN c1%NOTFOUND;
  end loop;
  
  open c2;
  loop
  fetch c2 into v_blah;
  EXIT WHEN c2%NOTFOUND;
  end loop;
  --This just lets you check the database is doing the right thing:
  select distinct link_name into v_link_name from pdu.link_name;
  dbms_output.put_line(v_link_name||' and '||v_blah);
  end ;
  </code></pre>