View Definition

Simple things like table aliases make a big difference when defining views. Consider the following examples.

SQL> create view X as select * from all_objects;

View created.

SQL> set long 5000
SQL> select text from user_views
  2  where view_name = 'X';

TEXT
--------------------------------------------------------------------------------
select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",
"SECONDARY" from all_objects

Oracle has expanded the list of columns explicitly, so if you later add a column to the table, the view will NOT contain the column until the view is defined.

But now consider when we add a table alias...

SQL> create view X1 as select a.* from all_objects a;

View created.

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

TEXT
--------------------------------------------------------------------------------
select a.* from all_objects a

Now the "*" is preserved.

Update

Thanks to K Parthasarathy for this additional information

In your View Definitions topic under General Administration in
http://www.oracledba.co.uk/, there was a mention about '*' being preserved
by using table alias.

It would be more useful, if the 'but-ifs' are clarified.   If the alias is
a table name, then, "any" alteration of the table (adding or removing a
column) will make the view unusable and has to be recreated.

SQL> CREATE TABLE TEMP_STATS1 AS SELECT ROWNUM col1, object_name, object_type FROM ALL_OBJECTS ;

Table created.

SQL> CREATE OR REPLACE VIEW temp_view AS SELECT a.* FROM TEMP_STATS1 a ;

View created.

SQL> SELECT text FROM ALL_VIEWS WHERE view_name = 'TEMP_VIEW'
  2  /

TEXT
-----------------------------------------------------------------------
SELECT a.* FROM TEMP_STATS1 a

SQL> SELECT * FROM TEMP_VIEW WHERE ROWNUM = 1;

      COL1 OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ ------------------
         1 /1005bd30_LnkdConstant         JAVA CLASS

SQL> ALTER TABLE temp_stats1 ADD dummy NUMBER ;

Table altered.

SQL> SELECT * FROM TEMP_VIEW WHERE ROWNUM = 1;
SELECT * FROM TEMP_VIEW WHERE ROWNUM = 1
              *
ERROR at line 1:
ORA-04063: view "CUSTOMER.TEMP_VIEW" has errors

SQL> show errors
No errors.

As this example shows, if a new column is added, then, the view becomes
unusable.  Same is the case if a column is deleted.
So, now, let's create a view without Alias.

SQL> CREATE OR REPLACE VIEW temp_view AS SELECT * FROM TEMP_STATS1 ;

View created.

And, create another view based on this view.   (Basically to show the
effect of an alias on a view instead of a table).

SQL> CREATE OR REPLACE VIEW temp_v_stats AS SELECT a.* FROM TEMP_VIEW a ;

View created.

SQL> ALTER TABLE temp_stats1 ADD dummy NUMBER ;

Table altered.

SQL> SELECT * FROM TEMP_V_STATS WHERE ROWNUM = 1 ;

      COL1 OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ ------------------
         1 /1005bd30_LnkdConstant         JAVA CLASS

SQL> ALTER TABLE temp_stats1 DROP COLUMN dummy;

Table altered.

Now, we can see that if a new column is added, then there is no change (as
expected and the new column is not displayed), but the temp_v_stats view is
still valid.   But, if a column is dropped, then, the new view
'temp_v_stats' becomes invalid.

SQL> CREATE OR REPLACE VIEW temp_view AS SELECT * FROM TEMP_STATS1 ;

View created.

SQL> SELECT * FROM TEMP_V_STATS WHERE ROWNUM = 1;
SELECT * FROM TEMP_V_STATS WHERE ROWNUM = 1
              *
ERROR at line 1:
ORA-04063: view "CUSTOMER.TEMP_V_STATS" has errors


SQL> spool off



So, though the view definition stores the '*' it is not really useful, and
infact becomes invalid when there is a change in the table.  Just wanted to
note that based on this note, if someone gets tempted to use this way
assuming it to be an advantage.

Update #2
Thanks for Ghassan Salem for this info.




I was reading the latest update under 'view definition' on oracledba/administration, and did give it a try under 10gRelease 2,
and when you create a view as select a.* from tttt a, then the view_text contains the full list of actual columns of tttt, and
an alter table tttt add column will not give any error in the table, but dropping a column will lead to an error on the view
(whether it is created with just select * or select a.*)