Need your or guidance on how I can execute a select on multiple databases provided in the list. the goal behind this code is to query multiple remote databases and insert the output in current database.
Need to db_link to be fetched from a list or table
insert into xxxx.DB_tracker value(SELECT d.name FROM v$database@**opXXX_du**);
Dynamic SQL.
Suppose that database links are stored in the link table:
SQL> select * From links;
LINK
---------
dbl_ora10
dbl_ora11
dbl_orcl
You'd then use a loop, create an insert statement and execute it. As I don't have those database links, I'm just displaying statements to the screen. You'd uncomment the execute immediate line.
SQL> set serveroutput on
SQL> declare
2 l_str varchar2(200);
3 begin
4 for cur_r in (select link from links) loop
5 l_str := 'insert into db_tracker ' ||
6 'select name from v$database@' || cur_r.link;
7 dbms_output.put_line(l_str);
8
9 -- execute immediate l_str;
10 end loop;
11 end;
12 /
insert into db_tracker select name from v$database@dbl_ora10
insert into db_tracker select name from v$database@dbl_ora11
insert into db_tracker select name from v$database@dbl_orcl
PL/SQL procedure successfully completed.
SQL>
If you want to actually select name and display it on the screen, then you need the into clause. Something like this:
SQL> set serveroutput on
SQL>
SQL> declare
2 l_name varchar2(30);
3 begin
4 for cur_r in (select link from links) loop
5 execute immediate 'select name from v$database@' || cur_r.link
6 into l_name;
7 dbms_output.put_line(l_name);
8 end loop;
9 end;
10 /
XE
PL/SQL procedure successfully completed.
SQL>
DBA_DB_LINKS(db_link column)has all information about db links but you need select privilege on this table also select privilege on table v$database in all remote db's.