Forum Stats

  • 3,759,024 Users
  • 2,251,494 Discussions
  • 7,870,473 Comments

Discussions

Query from random number of schemas.

kdwolf
kdwolf Member Posts: 103
edited Sep 15, 2008 6:48AM in General Database Discussions
Dear 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
Tagged:

Answers

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Of course, having your query dynamic implies every schema have the same table contents.
    However, I would avoid such query.
    What about a query (or MV) embedded the username where the data come from ?
    Then, you would be able the query against this view (or MV) within a WHERE clause like user in(user1,user2,...).
    Furthremore, what is the condition to query for one or other user ? Does it depend of the connected user you are query the table with, or is it a user choice ?

    Nicolas.
This discussion has been closed.