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.