How to auto-redefine view when underlying table changes (new column)?

Question

We've got a view that's defined like this

CREATE VIEW aView as 
SELECT * from aTable Where <bunch of conditions>;

The "value" of the view is in the where-condition, so it is okay to use a Select * in this case.

When a new column is added to the underlying table, we have to redefine the view with a

CREATE OR REPLACE FORCE VIEW aView as 
SELECT * from aTable Where <bunch of conditions>;

as the Select * seems to get "translated" into all the columns present at the time the view is (re-)defined.

My question: How can we avoid this extra step? (If the answer is dependent on the RDBMS, we're using Oracle.)

Answer 1

Hi IronGoofy,

This extra step is mandatory in Oracle: you will have to recompile your view manually.

As you have noticed, the "*" is lost once you create a view:

SQL> create table t (id number);

Table created

SQL> create view v as select * from t;

View created

SQL> select text from user_views where view_name = 'V';

TEXT
-------------------------------------------------------
select "ID" from t

Answer 2

You should not be using * in your views. Specify the columns explicitly.

That way you are only retrieving the data you need, and thus avoid potential issues down the road where someone adds a column to a table that you do not want that view to return (e.g., a large binary column that would adversely impact performance).

Yes, you need to recompile the view to add another column, but this is the correct process. That way you avoid other compilation issues, such as if the view reference two tables, and someone adds a duplicate column name in one of the tables. The compiler would then have issues determining which of the columns was being referred to if you did not prefix a reference to the column with a table alias, or it might complain if there are duplicate column names in the results.

Answer 3

The problem with automatically updating views to add columns comes when you extend your model, for example to

SELECT a.*, std_name_format(a.first_name, a.middle_names, a.last_name) long_name

or even

SELECT a.*, b.* from table_a a join table_b b....

If you have a view of just SELECT * FROM table, then you probably should be using a synonym or addressing the table directly.

If the view is hiding rows (SELECT * FROM table WHERE...), then you can look at the feature variously known as Fine Grained Access Control (FGAC), Row Level Security (RLS) or Virtual Private Database (VPD).

You might be able to do something with a DDL trigger but that would get complicated.