Hi there,
here’s just a small tip for those of you who are using Oracle’s materialized views with the refresh-on-commit option. Consider a following scenario:
- Create a table (let’s call it „X”) with a not-null column
- Create a materialized view („Y”) that has to refresh itself each time a record into the table „X” is inserted (or modified, deleted.. REFRESH FORCE ON COMMIT)
- Change the column created in point 1 to allow null values.
- Insert data into table „X”, with a null value of the column mentioned in point 1.
Most likely you’ll get an Oracle error, stating the following:
ORA-12008: error in materialized view refresh path
Further exception may also look similar to this:
ORA-01400: cannot insert NULL into …
The second one speaks for itself. Since the materialized view is actually a snapshot, it has „remembered” the not-null setting of the column of the table „X”. And when inserting a new record, it complains about the not-null constraint.
Drop the materialized view, re-create it, problem solved. The new one has the new column definition, allowing nulls in the column.
Hope this helps,
Łukasz