INSERT into a View with a GROUP BY Clause

When I wrote the previous post, about updatable views, I noticed an interesting issue.

The documentation says:

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

Let’s create a view with a GROUP BY clause and a group function:

ORA$BASE> create table t (
  2      x number,
  3      y date,
  4      z varchar2(100)
  5  );

Table created.

ORA$BASE> create or replace view v as
  2    select x, y, max(z) z from t
  3    group by x, y;

View created.

And now let’s see if Oracle “thinks” that this view is inherently updatable:

ORA$BASE> select column_name,updatable,insertable,deletable
  2    from user_updatable_columns
  3   where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
X                              NO        NO        NO
Y                              NO        NO        NO
Z                              NO        NO        NO

3 rows selected.

As expected, and in alignment with the documentation, all the columns of this view are not inherently updatable, insertable or deletable.

So the next step (obviously 🙂 ) is to try inserting a record into the view:

ORA$BASE> insert into v (x,y) values (1,date'2016-06-24');

1 row created.

ORA$BASE> insert into v (x,y) values (1,date'2016-06-24');

1 row created.

ORA$BASE> select * from t;

         X Y          Z
---------- ---------- ----------
         1 24/06/2016
         1 24/06/2016

ORA$BASE> select * from v;

         X Y          Z
---------- ---------- ----------
         1 24/06/2016

And… we succeed.

Tested in 11.2.0.4 and 12.1.0.2.

One Comment

  1. Iudith Mentzel

    Hello Oren,

    Very interesting 🙂

    My guess is that, for INSERT statements, Oracle is probably replacing the complete
    definition of the view with an inline view that SELECT-s from the table only the columns
    that we are effectively inserting into.

    If we try to also insert into the 3rd column, we get

    ORA-01733: virtual column not allowed here

    while if we try to update or delete, then we get the expected

    ORA-01732: data manipulation operation not legal on this view

    This maybe suggests that INSERT-s are treated slightly differently.

    Just for fun, I also tried a MERGE that would have resulted in an INSERT,
    and I got the second error, not the first one.

    The “insertability” of columns x, y does depend on the presence of column z,
    so this can be a reason for why the USER_UPDATABLE_COLUMNS cannot reflect the
    effective behavior observed.

    Best Regards,
    Iudith

Leave a Reply

Your email address will not be published.