Quirky expansion of Defining Queries at View Creation Time?

From: Marc Blum <marc_at_marcblum.de>
Date: Mon, 27 May 2002 18:59:57 GMT
Message-ID: <3cf2818f.2823520@news.online.de>

Version:
8.1.7.3 EE on NT4, W2K and SuSe Linux 7.0

Dear gurus,

found some differences on how Oracle expands column list of views with asterisk. Maybe someone shed some light on that? Comments in square brackets.

DROP VIEW v_simple;

View dropped

DROP VIEW v_alias;

View dropped

DROP TABLE t;

Table dropped

CREATE TABLE t
(n NUMBER,
 d DATE,
 v VARCHAR2(1));

Table created

[create one view without alias and one

 view with alias for the table]

CREATE OR REPLACE VIEW v_simple
AS
SELECT *
  FROM t;

View created

CREATE OR REPLACE VIEW v_alias
AS
SELECT a.*
  FROM t a;

View created

DESC t

Name Type Nullable Default Comments ---- ----------- -------- ------- --------

N    NUMBER      Y                         
D    DATE        Y                         
V    VARCHAR2(1) Y                         

DESC v_simple

Name Type Nullable Default Comments ---- ----------- -------- ------- --------

N    NUMBER      Y                         
D    DATE        Y                         
V    VARCHAR2(1) Y                         

DESC v_alias

Name Type Nullable Default Comments ---- ----------- -------- ------- --------

N    NUMBER      Y                         
D    DATE        Y                         
V    VARCHAR2(1) Y                         

[so far everything's fine]

SELECT text
  FROM user_views
 WHERE view_name = 'V_SIMPLE';

TEXT

---

SELECT "N","D","V"
  FROM t

SELECT text
  FROM user_views
 WHERE view_name = 'V_ALIAS';

TEXT

---

SELECT a.*
  FROM t a

[Look! Here the asterisk isn't expanded at all]

[go ahead and alter the table]

ALTER TABLE t

   ADD n2 NUMBER;

Table altered

SELECT object_name,status
  FROM user_objects
 WHERE object_name IN ('V_SIMPLE','V_ALIAS');

OBJECT_NAME STATUS

----------------------- -------
V_ALIAS                 INVALID
V_SIMPLE                INVALID

ALTER VIEW v_simple COMPILE;

View altered

[ok, v_simple continues to work]

ALTER VIEW v_alias COMPILE;

Warning: View altered with compilation errors

[hm..v_alias doesn't like that one]

DESC v_simple

Name Type Nullable Default Comments ---- ----------- -------- ------- --------

N    NUMBER      Y                         
D    DATE        Y                         
V    VARCHAR2(1) Y                         

SELECT text
  FROM user_views
 WHERE view_name = 'V_SIMPLE';

TEXT

---

SELECT "N","D","V"
  FROM t

DESC v_alias

Name Type Nullable Default Comments ---- --------- -------- ------- --------

N    UNDEFINED Y                         
D    UNDEFINED Y                         
V    UNDEFINED Y                         

SELECT text
  FROM user_views
 WHERE view_name = 'V_ALIAS';

TEXT

---

SELECT a.*
  FROM t a

[maybe I have to recreate v_alias?]

CREATE OR REPLACE VIEW v_alias
AS
SELECT a.*
  FROM t a;

View created

DESC v_alias

Name Type Nullable Default Comments ---- ----------- -------- ------- --------

N    NUMBER      Y                         
D    DATE        Y                         
V    VARCHAR2(1) Y                         
N2   NUMBER      Y                         

SELECT text
  FROM user_views
 WHERE view_name = 'V_ALIAS';

TEXT

---

SELECT a.*
  FROM t a

Why does v_simple expands and v_alias not? Why is v_simple compile-able and v_alias not?

Any ideas?

Thanx

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon May 27 2002 - 13:59:57 CDT