Inline View Check Option

Something I didn't know was possible, the "With Check Option" with an inline view. I knew this was possible with a "regular" view , but with an inline view...
The Check Option prohibits changes to the table (through the view) which would produce row that are not included in the view.
Let's start with a sample of how the Check Option works.

It is possible to perform DML operations using a view (some restrictions apply), as shown in the next example.

SQL> create table temp
  2  (x int);

Table created.

SQL> insert into temp values (1);

1 row created.

SQL> insert into temp values (100);

1 row created.

SQL> create view vw_temp
  2  as
  3  select *
  4    from temp
  5   where x <= 10
  6  ;

View created.

SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1

SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);

1 row created.

SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1
         2

What is quite noticable is that it is possible to insert records using the view which are not be visible through the view. To prevent this behaviour you can use the Check Option.

SQL> drop view vw_temp;

View dropped.

SQL>
SQL> create view vw_temp
  2  as
  3  select *
  4    from temp
  5   where x <= 10
  6   with check option
  7  ;

View created.

SQL>
SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1
         2

SQL>
SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);
insert into vw_temp values (102)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Now, with the Check Option in place it is not possible to do DML through the view if the records are not visible through the view in the first place. The value "102" would never show up in the result set when querying the view. Simply because the view has the predicate:

where x <= 10

This functionality has been around for a very, very long time.
What I didn't know, until recently, is that the same Check Option is also available with Inline Views.
First I'll show the INSERT using an Inline View, next the INSERT using an Inline View with Check Option.

SQL> create table test
  2  (x int)
  3  /

Table created.

SQL> insert into (select x
  2                 from test
  3              )
  4  values (100)
  5  /

1 row created.

SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4              )
  5  values (100)
  6  /

1 row created.

SQL>
SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4                with check option
  5              )
  6  values (100)
  7  /
               from test
                    *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

This also works well with bind variables, of course:

SQL> var b number
SQL> begin
  2     :b := 100;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4                with check option
  5              )
  6  values (:b)
  7  /
               from test
                    *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

Something similar to the Check Option is available when you use Virtual Private Database (VPD).
When you add a policy to implemnent your VPD, using the dbms_rls.add_policy procedure, one of the parameters is named "UPDATE_CHECK", which is similar to the Check Option.
Records which are not visible when the VPD policy is applied can't be manipulated when you pass in TRUE for this parameter.

All scripts in this post are executed in this version:

SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production