Oracle select schema depending on select-statement

Question

I have have two schemas with the same table. e.g schema1.adress and schema2.adress. Both tables are identical.

Layout of table customer: customerno: integer name: varchar2(50)

Now I want to get the customers of schema 1 using something like
"select * from customer where schemaname = 1" or
"select * from customer where schemaname = 2"

Is there a mechanism in Oracle that can switch the schema depending on a criteria in a select-statement?

Before you ask: for a new project I have to query legacy schemas. I cannot change the schema, but I can set any permission on the schema / user.

Any ideas?

Thanks for any response,

Sven

Answer 1

You could create a private synonym for the user for the schema.table you want them to access,

create synonym user1.customer for schemaname1.customer;
create synonym user2.customer for schemaname2.customer;

Then your queries would always just be select * from customer;

Answer 2

So you are logged in as the same user both when you want to select from schema1.customer and select from schema2.customer?

A possible way can be something like:

select *
  from (
   select 'schema1' schema$name, c.* from schema1.customer c
   union all
   select 'schema2' schema$name, c.* from schema2.customer c
  )
 where schema$name = 'schema1'

If you can create views, it can be an idea to create a view like:

create or replace view customer_view as
   select 'schema1' schema$name, c.* from schema1.customer c
   union all
   select 'schema2' schema$name, c.* from schema2.customer c

That makes it possible to do:

select *
  from customer_view
 where schema$name = 'schema1'

Whether you use view or not, Oracle optimizer can in most cases like this push the predicate "where schema$name = 'schema1'" into the UNION ALL and then it recognizes that it does not need to access schema2.customer at all.