Thanks for the question, Kris.
Asked: August 05, 2020 - 5:09 pm UTC
Answered by: Connor McDonald - Last updated: August 06, 2020 - 8:56 am UTC
Category: SQL - Version: Oracle 12c Release 1
Viewed 100+ times
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.
and we 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.