I am trying to execute the query for different db links. I am using a for loop concatenated dblink. But it is throwing a db link expected error.
begin
for i in (select db_link from db_table )
loop
select * FROM table@||i.db_link;
end loop;
end;
Asked
Active
Viewed 126 times
0
-
Its a sql query.... – Learner Oct 16 '18 at 08:29
-
oracle normal sql query... – Learner Oct 16 '18 at 08:32
-
what do you want to achieve – Nikhil S Oct 16 '18 at 08:47
-
I want to run same query in different db's. So using for loop one by one db I am taking and concatinating with table name in the place of dblink.But still it's throwing dblink expected error – Learner Oct 16 '18 at 08:54
-
Thanks for edits now can anybody help me in solving this¿? – Learner Oct 16 '18 at 10:02
-
Please search for CURSOR processing in PL/SQL, espetially *dynamic cursor*, which you will need for parametrizing the DB link. – Marmite Bomber Oct 16 '18 at 10:50
-
Can u give me an example how can I achieve that?? – Learner Oct 16 '18 at 11:47
-
declare vsql varchar2(2000); begin for i in (select db_link from db_table) loop vsql:='select * FROM table@'||i.db_link; execute immediate 'vsql'; end loop; end; itried above it'snot throwing any error but not getting any data... – Learner Oct 16 '18 at 11:54
-
In PL/SQL blocks, you have to tell it what you want to print to output. https://stackoverflow.com/questions/351489/is-it-possible-to-output-a-select-statement-from-a-pl-sql-block – kfinity Oct 16 '18 at 13:14
-
Kfinity -didn't get how it solve my case – Learner Oct 17 '18 at 08:27