Extra column in Materialized view but not in the query definition

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Sun, 28 Aug 2005 01:13:43 -0400
Message-ID: <f09dd62805082722137a749834@mail.gmail.com>

Could someone tell me how "TAX_CATEGORY_VALUE.ALIAS" column is listed in my Materialized View.

There are some packages refering to TAX_CATEGORY_VALUE.ALIAS column.

The package is VALID since we have this column listed in 8i, however after migrating to 9i the package becomes INVALID because the column is not listed anymore.

>From 8i:

---

SQL> select owner,object_name,object_type from dba_objects where object_name='TAX_CATEGORY_VALUE';  

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------------------ 
------------------

PUBLIC TAX_CATEGORY_VALUE SYNONYM
HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE
HIBM_TAXONOMY TAX_CATEGORY_VALUE UNDEFINED   SQL> select query from dba_mviews where MVIEW_NAME='TAX_CATEGORY_VALUE';  

QUERY

---

SELECT "TAX_CATEGORY_VALUE"."ID" "ID","TAX_CATEGORY_VALUE"."NAME_ID" "NAME_ID","
TAX_CATEGORY_VALUE"."VALUE" "VALUE","TAX_CATEGORY_VALUE"."IS_ACTIVE" "IS_ACTIVE"
 FROM "TAX_CATEGORY_VALUE"@Q2HIBM.US.HSBC.COM <http://Q2HIBM.US.HSBC.COM>"TAX_CATEGORY_VALUE"    

SQL> desc TAX_CATEGORY_VALUE
 Name Null? Type

• --------

  ---

  ID NOT NULL NUMBER(10) NAME_ID NOT NULL NUMBER(10) VALUE NOT NULL VARCHAR2(100) IS_ACTIVE NUMBER(38) ALIAS NUMBER(10)

SQL>
>From 9i:

---

SQL> select owner,object_name,object_type from dba_objects where object_name='TAX_CATEGORY_VALUE';  

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------------------ 
------------------

PUBLIC TAX_CATEGORY_VALUE SYNONYM
HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE
HIBM_TAXONOMY TAX_CATEGORY_VALUE MATERIALIZED VIEW   SQL>
SQL> desc TAX_CATEGORY_VALUE
 Name Null? Type

• --------

  ---

  ID NOT NULL NUMBER(10) NAME_ID NOT NULL NUMBER(10) VALUE NOT NULL VARCHAR2(100) IS_ACTIVE NUMBER(38)

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Aug 28 2005 - 00:15:50 CDT