No Use Select * In Create View

A very old rule in Oracle has always been not user “select *” clause to create a view, technically can be done, but if you take a look at the following example;

 TE*ANTO> create view anr_view as select * from anr_dba_users; 

Being anr_users a copy of the dba_users.

Create the next view using “select *” clause;

 
TE*ANTO> create view anr_view as select * from anr_dba_users;

We looked at the definition of the view in the dba_views;

 
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';

TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME" from anr_dba_users

Oracle translates the “select *” to all the columns that exist in the table at the time of the creation of the view.
Now a problem is when you add columns or delete columns.

 TE*ANTO> ALTER TABLE ANR_DBA_USERS ADD (NEWCOL VARCHAR2(10)); Vista creada. TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW'; TEXT ________________________________________________________________________________ select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE ","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_ CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users 

The table has changed but continued the same view, use a compile does not solve the problem. The only way is to do a create/rebuild;

 
TE*ANTO> CREATE OR REPLACE
  2  view anr_view as select * from anr_dba_users;

Vista creada.

Transcurrido: 00:00:00.21
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';

TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users

If we look we see that the new column NEWCOL longer appears.
Recommendation: Always use the fields you want to use or need in the definition of the view.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s