Updating an oracle view

I was recently asked by a former colleague "Can you insert data in an Oracle table through a View?

"In some cases, yes you can, and it actually serves as a handy technique to manage security on your data.

You can explicitly prevent users from modifying the base table through your View by creating it with the WITH READ ONLY clause.

That signals to Oracle that your View is meant for querying only.

Can I rewrite the above with join, and still be able to update/insert the view?

All the columns from all_updatable_columns show they are not updatable! Moving onto your other one -- if the right keys are in place AND we drop the NVL() which clouds the issue -- the view can be key preserved.

For example, and along the same lines of reasoning, if the View contains a SET, DISTINCT, GROUP BY, GROUP, ORDER BY, CONNECT BY, START WITH, then Oracle can't perform any update or insert operations on the View.

Same goes if you have a collection expression or a subquery in a SELECT list.

When you're executing DML operations on a View, Oracle actually executes those DML operations appropriately on the base tables.You can clearly see that I did not do such a good job with this. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement.Sometimes coming up with a short descriptive name for your view is easier said than done. You can also see that we have take the birth date column and calculated age. CLASSES_NUM / Above is a simple view that provides us with a listing of occupied/unoccupied seats for our classes.Simply put, you may be able to update one base table through the view, provided your join clause uses a unique index. To learn more on this, read Norman Dunbar's article in Jonathan Lewis' Oracle User's Co-Operative FAQ.Briefly put, you can create a update trigger on that view to update the data in the base tables appropriately.

Search for updating an oracle view:

updating an oracle view-5

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating an oracle view”

  1. Author of The Stranger New York Times bestselling author of JUST ONE NIGHT, DECEPTIVE INNOCENCE, the Sophie Katz mysteries, SO MUCH FOR MY HAPPY ENDING and the upcoming DANGEROUS ALLIANCE, JUST ONCE MORE and JUST ONE LIE (July 28th, 2015). She's not perfect—you aren't either, and the two of you may never be perfect together but if she can make you laugh, cause you to think twice, and admit to being human and making mistakes, hold onto her and give her the most you can. She may not be thinking about you every second of the day, but she will give you a part of her that she knows you can break—her heart.