Re: Quirky expansion of Defining Queries at View Creation Time?

On Mon, 27 May 2002 18:59:57 GMT, marc_at_marcblum.de (Marc Blum) wrote:

>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

Oracle clearly advises against using the *, as that would be resolved at compile-time only. The best solution would be NOT to use the *

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon May 27 2002 - 15:42:38 CDT