Re: Table inheritance in Oracle?

No - the syntax of the CREATE VIEW statement does not allow this. A view is a SQL statement that gets constructed everytime someone queries against the view. You need tables to run queries against, not abstract "templates".

Why not just give H, D, and T their own table? All the tables can have the same name. Creating a "template" view (if there was such a thing) seems to be only adding complexity on top of the simple concept of each schema owner having their own sets of tables.

Why would you want to do this? Just curious.

In article <39206634.0_at_news.dca.net>,
  "Tim Romano" <tim_at_dca.net> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 15 2000 - 00:00:00 CDT