CODEWITHSUNDEEP
oracle-databasevariablesplsqlproceduredblink
Scouse_Bob
Scouse_Bob

Reputation: 546

How do I pass a database link as a variable in a stored procedure

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?

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

Upvotes: 0

Views: 2385

Answers (1)

q4za4
q4za4

Reputation: 652

Using cursor to get one row is... Just try to avoid.

 create or replace procedure TEST_LINK 
 is
   v_blah      varchar2(10);
   v_link      varchar2(10);
 begin

 select link_name
 into   v_link
 from   link_name;

 execute immediate 'select count(1) from v$database@'||v_link
 into  v_blah;

 dbms_output.put_line(v_link||' and '||v_blah);
 end test_link;

Upvotes: 3

Related Questions