Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: Incorrect view definition in DBA_VIEWS for "SELECT x.*" type views

    Hi,

    We have a case in Oracle server (version 9.2.0.6) that behaves differently than other Oracle servers we have.

    Below there is a test case with two answers got from two Oracle servers:

    drop table t1;
    create table t1(c1 number, c2 number);
    create or replace view v1 as select t1.* from t1;
    select text from user_views where view_name like 'V1';

    One server result was defining a “select t1.* from t1” in a view and one defining “select t1.c1, t1.c2 from t1” for the same view.

    Server 1:
    ====================
    drop table t1 succeeded.

    create table succeeded.

    create or succeeded.

    drop table t1 succeeded.

    create table succeeded.

    create or succeeded.

    TEXT
    ----
    select t1.* from t1

    1 rows selected




    Server 2 result:
    ====================
    drop table t1 succeeded.

    create table succeeded.

    create or succeeded.

    TEXT
    ----
    select t1."C1",t1."C2" from t1

    1 rows selected


    Questions
    ==================
    A) What is the correct behavior (I guess the “select t1.c1, t1.c2”)?
    B) Is there a related bug (other than 4192148 and 4212516 – we do not have corruption and core dumps)?


    Thanks,

    Tal Olier
    (tal.olier@gmail.com)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I see the “select t1.c1, t1.c2” form in database versions 9.2.0.6 and 10.1.0.4. Perhaps older versions used to show "select *".

    The SQL Reference says this:
    If the subquery uses an asterisk (*) to select all columns of a table, and you later add new columns to the table, then the view will not contain those columns until you re-create the view by issuing a CREATE OR REPLACE VIEW statement.
    My guess is that USER_VIEWS has been enhanced to show the list of columns actually used to create the view rather than the ambiguous "*" specified in the CREATE VIEW statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •