WyhULDMe

1declare
2    v_result varchar2(4000);
3begin
4    --Loop through a configuration table of links.
5    for links in
6    (
7        select database_name, db_link
8        from dbs_to_monitor
9        left join user_db_links
10            on dbs_to_monitor.database_name = user_db_links.db_link
11        order by database_name
12    ) loop
13        --Run the query if the link exists.
14        if links.db_link is not null then
15            begin
16                --Note the user of REPLACE and the alternative quoting mechanism, q'[...]';
17                --This looks a bit silly with this small example, but in a real-life query
18                --it avoids concatenation hell and makes the query much easier to read. 
19                execute immediate replace(q'[
20                    select dummy from dual@#DB_LINK#
21                ]',
22                '#DB_LINK#', links.db_link)
23                into v_result;
24
25                dbms_output.put_line('Result: '||v_result);
26            --Catch errors if the links are broken or some other error happens.
27            exception when others then
28                dbms_output.put_line('Error with '||links.db_link||': '||sqlerrm);
29            end;
30        --Error if the link was not created.
31        --You will have to run:
32        --create database link LINK_NAME connect to USERNAME identified by "PASSWORD" using 'TNS_STRING'; 
33        else
34            dbms_output.put_line('ERROR - '||links.db_link||' does not exist!');
35        end if;
36    end loop;
37end;
38/