Query from random number of schemas.
kdwolfSep 15 2008 — edited Sep 15 2008Dear All,
I need to run a query for a given my_table at once from a number of schemas :
SELECT *
FROM my_schema1.my_table
UNION ALL
SELECT *
FROM my_schema2.my_table
And I need it to be a dynamic one, as I see another client asking for the same.
Is there any better idea than using of a
FOR ... LOOP (SELECT du.username FROM dba_users du WHERE du.username LIKE 'my_schema%')
and then EXECUTE IMMEDIATE per each record in this LOOP?
Many thanks