Table inheritance in Oracle?

When creating a view in one schema for use in several other schemas, is there any directive that tells Oracle to look for a local table in the current schema, of the same name as the table in the schema where the view was created?

That is, is it possible in Oracle to create a view in what might be thought of as a "abstract schema" and then grant select against the view to various other schema-owners, each of whom has real tables of the same name and structure as the abstract table, so that when the various schema owners select against the view, theyy select against their own table?

If that question is unclear, let me give you a brief example. Note: schema owners Tom, Dick, and Harry can be thought of as different departments or operating companies, each with its own set of data. They're not really individual users. They could have been called Franchise1, Franchise2, and Franchise3 just as easily.

Schema: Template
assume existence of a table (containing no data) called Sales (i.e. Template.Sales)

create or replace view vSales as
select * from Template.Sales

grant select on vSales to Tom
grant select on vSales to Dick
grant select on vSales to Harry

Schema: Tom
assume existence of table called Sales (i.e. Tom.Sales)

Schema: Dick
assume existence of table called Sales (i.e. Dick.Sales)

Schema: Harry
assume existence of table called Sales (i.e. Harry.Sales)

What I want is this: when Tom, Dick, or Harry selects against view vSales, they select against Tom.Sales, Dick.Sales, or Harry.Sales respectively. In other words, I want these schemas to "inherit" the template schema's view, and I want to treat table Template.Sales merely as an abstract placeholder for which a table of the same name, in the current schema, is substituted.

Thanks
Tim Romano Received on Mon May 15 2000 - 00:00:00 CDT