Views Don’t Care Unless They Have To

Oracle’s Edition Based Redefinition (EBR) feature allows modifying database objects such as PLSQL code or database tables without interfering with a functioning application. In this series of articles I’ll introduce some of the underlying concepts of EBR and show some examples of its use. This first post describes a feature of Oracle views that helps enable EBR – the fact that views do not automatically change when their underlying tables do. Here is an example:

-- create demo table
SQL> CREATE TABLE demo
 2 ( col1 NUMBER );
Table created.

-- create a view on top of the table
SQL> CREATE VIEW demo_view AS
 2 SELECT *
 3 FROM demo;
View created.

-- insert a record into the view and only provdide value for col1
SQL> INSERT INTO demo_view
 2 VALUES(1);
1 row created.

-- add a second column to the table
SQL> ALTER TABLE demo
 2 ADD ( col2 NUMBER );
Table altered.

-- Does the view see the new column right away? No!
SQL> INSERT INTO demo_view
 2 VALUES(2);
1 row created.

-- The view remains blissfully unaware
SQL> SELECT *
 2 FROM demo_view;
COL1
----------
 1
 2

-- the table sure knows about the column though
SQL> INSERT INTO demo
 2 VALUES(3);
INSERT INTO demo
 *
ERROR at line 1:
ORA-00947: not enough values

Inserts directly into the table fail because of the new column. But not the view! The view does not see the columns until it is recreated.

SQL> CREATE OR REPLACE VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

SQL> INSERT INTO demo_view
 2   VALUES(3);
INSERT INTO demo_view
 *
ERROR at line 1:
ORA-00947: not enough values

In future articles I’ll explain how this relates to EBR.

Thanks for reading!