Thanks for the question, Kris.
Asked: August 05, 2020 - 5:09 pm UTC
Last updated: August 06, 2020 - 8:56 am UTC
Version: Oracle 12c Release 1
Viewed 1000+ times
You Asked
Hi,
I am not sure whether or not WITH READ ONLY and WITH CHECK OPTION syntaxes used for views and tables only. I read on Oracle Help Center with the following link
https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30 It is said that:
WITH READ ONLY
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
I hope to receive your response.
Thanks,
Kris.
and Connor said...
Yes, in my experience exclusively with views.
People often forget that views can be updatable and hence open up the chance of changing the base table, even if that view contains a join.
So most views should probably be created with the READ ONLY option by default, and if they are intended to be updated, then the WITH CHECK ensure people don't update "out of their area" so to speak.
Is this answer out of date? If it is, please let us know via a Comment