Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating a (complex) view

Re: Updating a (complex) view

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/04/06
Message-ID: <3346ffe2.1520195@newshost>#1/1

It depends on the version. In 7.3 many of the join view issues are relaxed and under certain conditions you can update a join view.

Your example however would be updatable in all versions if you use EXISTS instead of a join view. For example:

create view myview
as
select x, y, z
  from mytable
 where exists ( select NULL from myothertable where p = mytable.x );

On Sat, 05 Apr 1997 09:21:09 -0800, Venu Subramaniam <venusub_at_popd.ix.netcom.com> wrote:

>I am pretty new to Oracle and am designing a 2-tier architecture which
>uses View-based security.
>
>I am trying to update a View which is using columns from one underlying
>table but references a column from another table. Is this a complex
>view? I am not able to Update such a View. Let me give an example.
>
>If my view is composed by
>
>CREATE VIEW MYVIEW AS
>SELECT x, y, z from MYTABLE, MYOTHERTABLE where x=MYOTHERTABLE.p;
>
>In the above View, the columns x,y,z all come from MYTABLE and a
>reference is made to column p in MYOTHERTABLE.
>
>I am not able to Update MYVIEW. Can I do this in any other way?
>Security is my primary concern and I'd prefer users not to update any
>tables directly.
>Thanks a lot for any help.
>- Venu

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Apr 06 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US