2 Replies Latest reply on Mar 5, 2004 5:28 AM by Andrew May

    CMP on a View?

    Dan Thompson Newbie

      Is it possible to define a CMP on a view instead of a table? Are there limitations?

      dt

        • 1. Re: CMP on a View?
          Dan Thompson Newbie

          see, even though there is some information on this topic in the newbie area, it doesn't address the fact that the later Oracle releases will let you write to a view. Anyway, we've been trying, and failing, all day to make EJBs writeable using views.

          • 2. Re: CMP on a View?
            Andrew May Newbie

            We're using CMP with views (Oracle 8.1.7.4 I think) and we're able to update them.

            However, our views are really just hidding certain things from the entities (e.g. there are lastupdated dates and things like that which aren't included in the view).

            If you've got a column which is derived in the view you won't be able to update it (I think that's possible in newer versions of Oracle and possibly the Enterprise edition of Oracle 8i ???), but what you can do is use a trigger to catch the update and make the appropriate changes.

            So I think CMP will have the same issues as any user of the view.

            One thing we have had problems with is where we delete from our views we're actually just changing a state in the table "behind the scenes" and the records no longer appear in the view. If you're using Container Managed Relationships and you do something similar you've got to be careful that you don't have entries in other tables/views that still point to this - your tables may still have referential integrity (i.e. the Foreign Keys still apply), but the views might not. This isn't a problem if you properly delete the related entities (or get the CMP engine to do it for you), but if someone updates the state directly in the database you can have problems.

            I hope that's useful. p.s. I'm a Java developer and not a DBA, so I'm no expert and I hope that's all correct. Also there's probably lots of other fancy features that I'm not aware of.

            -Andrew