Re: Views in ORACLE 7.3

From: N.Prabhakar <prabhs_at_pacific.net.sg>
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

Original text of this message