OracleBlog

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. Here's what I mean: you can restrict access to a base table, and then create a view containing only rows and columns that you wish to be accessible. That is just one of the many handy uses of views. Quick hint: read up on the WITH CHECK OPTION to prevent changes to base table rows to which you're trying to restrict access.

Now why did I say "in some cases?" Because inserting/updating data in a View depends on the View. Why? Well a View is just a stored query against some base tables. When you're executing DML operations on a View, Oracle actually executes those DML operations appropriately on the base tables. And there are some cases where it is impossible for Oracle to do that. Let's look at one simple example.

-- Create our data
CREATE TABLE BaseTable (field1 NUMBER(8), field2 NUMBER(8));
INSERT INTO BaseTable (field1, field2) VALUES (5, 10);
CREATE OR REPLACE VIEW UpdateViewYes 
AS SELECT field1, field2 FROM BaseTable;
CREATE OR REPLACE VIEW UpdateViewNo 
AS SELECT field1 + field2 addfield FROM BaseTable;

-- This won't work
SQL> UPDATE UpdateViewNo SET addfield = 16;
UPDATE UpdateViewNo SET addfield = 16
                        *
ERROR at line 1:
ORA-01733: virtual column not allowed here

-- This should work
UPDATE UpdateViewYes SET field1 = 6 WHERE field1 = 5;
SQL> UPDATE UpdateViewYes SET field1 = 6 WHERE field1 = 5;

1 row updated.

-- Look at our data now
SQL> SELECT * FROM BaseTable;

    FIELD1     FIELD2
---------- ----------
         6         10

In this example, UpdateViewNo.addfield is the addition of two columns. How would Oracle know how to divide the new value we assigned? This argument would hold for any situation where the View had a pseudocolumn or expression column, like this example, or perhaps one created through the use of DECODE. Of course, these rows could still be deleted.

The Oracle SQL Reference details other situations where your View couldn't be updated. 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.

USER_UPDATABALE_COLUMNS

Not sure if your View can be updated or not? Well you can simply try and see, but there is another way. Find your View in the USER_UPDATABLE_COLUMNS table and see for yourself.

SQL> SELECT table_name, column_name, updatable
  2  FROM user_updatable_columns
  3  WHERE table_name LIKE 'UPDATEVIEW%';

TABLE_NAME                COLUMN_NAME               UPD
------------------------- ------------------------- ---
UPDATEVIEWNO              ADDFIELD                  NO
UPDATEVIEWYES             FIELD1                    YES
UPDATEVIEWYES             FIELD2                    YES

WITH READ ONLY

What if you don't want anyone to update obase tables using your View? 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.

Join Views

Thus far I've been talking only about cases where the View is based on a single base table. What about when the View joins one or more base tables? This is referred to as a Join View.

Updating join views is a whole different ball of wax. Simply put, you may be able to update one base table through the view, provided your join clause uses a unique index. Otherwise, there are workarounds. 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. There is also an example on Building Complex Updatable Views Using Triggers in the Application Developer's Guide, Chapter 15.

For more information on Views, including a list of what disqualifies a view from being updateable, a description of Join Views, details of the WITH READ ONLY clause, examples and much more, consult the Oracle SQL Reference, Chapter 16.