Re: Views in ORACLE 7.3
Date: 1996/12/04
Message-ID: <582i53$50f_at_newton.pacific.net.sg>#1/1
Roger Wernersson <roger.wernersson_at_adra.se> writes: > nkimtong_at_starnet.gov.sg wrote:
> >
> > Hi
> > Can someone tell me is it possible to update view based on more than one table in ORACLE 7.3?
> >
>
> Yes, I can. No, it's not.
>
> /Roger
> --
> Sport radio: people listening to people watching people having fun
> Mailto:roger.wernersson_at_adra.se
> BTW: All opinions are mine, all mine, and nobody's but mine.
Hi there,
YES. You can update views which are based on multiple table. Read the following paragraph
Views that involve joins are updatable in this release under certain conditions. ROWIDs can be selected from join views in some cases. This material clarifies material in the Oracle7 Server SQL Reference. Specifically, it affects the functionality of the INSERT, UPDATE, and DELETE statements and supplements the description of view updatability in the CREATE VIEW statement.
Definitions
Prior to Release 7.3, views containing joins were not updatable. In Release 7.3, they are updatable under specified conditions. The following defines these conditions:
o A join column is a column that is used to form comparisons between the values in the tables in a join. The most common sort of join, called an equijoin, compares two columns, one from each table joined, and limits the results of the join to those combinations of rows that make the values in these two columns equal.
o A join view is a view that contains a join.
o A key-preserved table is a table in a join view, all of whose key columns are present as keys in the join view. This means the keys must not only be in the join view, but must still be unique and not null in the join view. This implies that a key-preserved table generally cannot be an outer-joined table. Such would be possible only if the outer join did not in fact generate any nulls, which is a function of the data and therefore inadmissible as a basis for operations.
For further clarification of these definitions, see CREATE VIEW (for views) and SELECT (for joins) in the Oracle7 Server SQL Reference.
Criteria
Given the above, you can execute the DML statements - INSERT, UPDATE, and DELETE - on a join view provided that all of the following are true
o The DML statement affects only one of the tables underlying the join.
o If the statement is UPDATE, then all columns updated are extracted from a key-preserved table. In addition, if the view has the CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view are shielded from UPDATE.
o If the statement is DELETE, then there is one and only one key-preserved table in the join. This table may be present more than once in the join, unless the view has the CHECK OPTION.
o If the statement is INSERT, then all columns into which values are inserted come from a key-preserved table and the view does not have the CHECK OPTION.
Basically I copied the above portion from README.doc of Oracle 7.3.
Hope the info helps,
Regards
N.Prabhakar Received on Wed Dec 04 1996 - 00:00:00 CET