Обсуждение: accessing multiple databases using dblink
Good morning to everybody, I've to resolve this situation: I've a collection of many different databases, all identical, and the name of those databasesis stored inside a table in another "central management" database. In an ideal world, I'd like with a single query to be able to recover the name of each database from the table that doescontains them, and use it to do a query on each table on each databse joining the results. At the moment I'm doing it by external code, but performaces are awfull... At first I tough something similar to select * from tab_databases,dblink('dbname=' || tab_databases.name,'select count(id) from tab_data') could work, but it seems its not allowed to reference rows from another table inside the from level. Putting dblink outside that level allow me to use the dynamic dbname, but in that case I would need a row resource, whiledblink give back a table resource... Is it possible to resolve this inside the databse? Thanks a lot...
On 12/13/07, f.zamboni@mastertraining <f.zamboni@mastertraining.it> wrote: > Good morning to everybody, > I've to resolve this situation: I've a collection of many different databases, all identical, and the name of those databasesis stored inside a table in another "central > management" database. > In an ideal world, I'd like with a single query to be able to recover the name of each database from the table that doescontains them, and use it to do a query on each table on > each databse joining the results. > At the moment I'm doing it by external code, but performaces are awfull... > At first I tough something similar to > select * from tab_databases,dblink('dbname=' || tab_databases.name,'select count(id) from tab_data') > could work, but it seems its not allowed to reference rows from another table inside the from level. > > Putting dblink outside that level allow me to use the dynamic dbname, but in that case I would need a row resource, whiledblink give back a table resource... > > Is it possible to resolve this inside the databse? > Thanks a lot... This seems to be appropriate task for pl/proxy: https://developer.skype.com/SkypeGarage/DbProjects/PlProxy -- marko