create view with asterisk

[ADENAS51]

Database Release or Database Parameter ?

In the MARS database : 9.2.0.5
for the view fred_01 the asterik (*) gets expanded and stored as a comma-separated list of column names.
but not for the view fred_02 with a.*

SQL> connect system/manager@MARS
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as system

SQL> create or replace view fred_01 as select * from cat;

View created

SQL> create or replace view fred_02 as select a.* from cat a;

View created

SQL> select * from user_views where view_name like 'FRED%';

VIEW_NAME TEXT_LENGTH TEXT
------------------------------ ----------- ------------------------------------------
FRED_01 42 select "TABLE_NAME","TABLE_TYPE" from cat
FRED_02 22 select a.* from cat a


In the VENUS database : 9.2.0.7
fred_03 (*) and fred_04 (a.*) get expanded ... Why ?


SQL> connect system/manager@VENUS
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as system

SQL> create or replace view fred_03 as select * from cat;

View created

SQL> create or replace view fred_04 as select a.* from cat a;

View created

SQL> select * from user_views where view_name like 'FRED%';

VIEW_NAME TEXT_LENGTH TEXT
------------------------------ ----------- -------------------------------------------------
FRED_03 42 select "TABLE_NAME","TABLE_TYPE" from cat
FRED_04 48 select a."TABLE_NAME",a."TABLE_TYPE" from cat a

 

[LKBrwnDBA]

And the question is????


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ADENAS51]

I need to keep the original code of the view
inside the database ( development database )

The question is :
Why is there a difference between the 2 databases ?
Is it a parameter or only the database Release ?

Thanks !

 

[LKBrwnDBA]

It is a version/release issue, check out Oracle 11.2:

SQL> Select * From V$Version
  2  /

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> Create Or Replace View V01 As Select * From Cat
  2  /

View created.

SQL> Create Or Replace View V02 As Select A.* From Cat A
  2  /

View created.

SQL> Select View_Name, Text_Length, Text
  2  From User_Views Where View_Name Like 'V0_'
  3  /

VIEW_NAM TEXT_LENGTH TEXT
-------- ----------- ------------------------------------------------------
V01               41 Select "TABLE_NAME","TABLE_TYPE" From Cat
V02               47 Select A."TABLE_NAME",A."TABLE_TYPE" From Cat A

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb