Misconceptions about (Regular) Views Revealed when Presenting Editioning Views

Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.

It happens to me almost every time I talk about Edition-Based Redefinition. I present Editioning Views, and then I get questions that reveal misunderstandings about views in general.

One such misunderstanding is regarding what is kept in the view definition.

When we create a view as “select * from table”, the * is expanded to actual columns during the view creation. The * is not kept as part of the view definition.

For example:

> create table people (
>    person_id integer not null,
>    first_name varchar2(20),
>    last_name varchar2(20)
> );

Table created.

> create or replace view people_v as select * from people;

View created.

> select text from user_views where view_name='PEOPLE_V';

TEXT
--------------------------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME" from people

> select column_id,column_name,nullable,data_type 
>   from user_tab_columns 
>  where table_name='PEOPLE_V'
>  order by column_id;

 COLUMN_ID COLUMN_NAME          NUL DATA_TYPE
---------- -------------------- --- ----------
         1 PERSON_ID            N   NUMBER
         2 FIRST_NAME           Y   VARCHAR2
         3 LAST_NAME            Y   VARCHAR2

If we add a new column to the PEOPLE table, the PEOPLE_V view will not be affected (as the column list in its definition is not “*”).

> alter table people add (eye_color varchar2(10));

Table altered.

> select text from user_views where view_name='PEOPLE_V';

TEXT
--------------------------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME" from people

> select column_id,column_name,nullable,data_type 
>   from user_tab_columns 
>  where table_name='PEOPLE_V' 
>  order by column_id;

COLUMN_ID COLUMN_NAME          NUL DATA_TYPE
---------- -------------------- --- ----------
1 PERSON_ID            N   NUMBER
2 FIRST_NAME           Y   VARCHAR2
3 LAST_NAME            Y   VARCHAR2

There is one case though where the * is not expanded during the view creation – if we (forcibly) create a view with invalid SELECT statement:

> create or replace force view people_v as 
>   select * from people
>    where non_exiting_column is not null;

Warning: View created with compilation errors.

> select text from user_views where view_name='PEOPLE_V';

TEXT
------------------------------------------------------------
select * from people where non_exiting_column is not null

> select * from people_v;

select * from people_v
*
ERROR at line 1:
ORA-04063: view "DEMO.PEOPLE_V" has errors

But as soon as the view becomes valid, the * is expanded and the actual column list is stored in the view definition:

> create or replace force view people_v as 
>   select * from people 
>    where last_name is not null;

View created.

> select text from user_views where view_name='PEOPLE_V';

TEXT
------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME","EYE_COLOR" from
people where last_name is not null

In the next post I’ll discuss another misconception about views.