When Views Are Forced To Care

In my last post regarding Edition Based Redefinition (EBR) I explained that Oracle views do not automatically take on changes to the structures underneath them. This makes them a very handy feature. But they do have limitations – such as when an new NOT NULL column is added. Here is an example:

-- create a table with just one lonely column
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.

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

-- try to insert a record into the view
SQL> INSERT INTO demo_view
 2   VALUES(2);
INSERT INTO demo_view
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DRH"."DEMO"."COL2")

The INSERT does not succeed because a value is not provided for COL2 in the DEMO table.

In the next few post’s I’ll explain how this failure might be worked around using a methodology that requires downtime.

Then I’ll segue into how EBR handles it with minimal downtime.

Thanks for reading!