From oracle-l-bounce@freelists.org Sun Aug 28 00:15:50 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j7S5Fmas016852 for ; Sun, 28 Aug 2005 00:15:49 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j7S5FdIP016839 for ; Sun, 28 Aug 2005 00:15:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 096D61E6F15; Sun, 28 Aug 2005 00:15:38 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 05136-09; Sun, 28 Aug 2005 00:15:37 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 771BF1E636A; Sun, 28 Aug 2005 00:15:37 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=TlxEhT+FIA959psDiK3JoZjAIWMak6wynAfmj3rsDNaC1Hgu5APIao099gw3mR+OzpPo/pns/UuCOdT5i5uyj/ZkC8h27+E6kA7VVRiEL6XSrpZj83YqgBm5aKLpNTyw09eM1xcGBAg3fkzc3txpsPAJnzLBoQJzoH4QZs9nsOI= Message-ID: Date: Sun, 28 Aug 2005 01:13:43 -0400 From: Sami Seerangan To: oracle-l Subject: Extra column in Materialized view but not in the query definition Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1092_21068614.1125206023564" X-archive-position: 24614 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dba.orcl@gmail.com Precedence: normal Reply-To: dba.orcl@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.0 required=5.0 tests=AWL,BAYES_00,HTML_60_70, HTML_MESSAGE,UPPERCASE_50_75 autolearn=no version=2.63 ------=_Part_1092_21068614.1125206023564 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Could someone tell me how "TAX_CATEGORY_VALUE.ALIAS" column is listed in my= =20 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= =20 migrating to 9i the package becomes INVALID because the column is not liste= d=20 anymore. >From 8i: =3D=3D=3D=3D=3D=3D=3D=3D SQL> select owner,object_name,object_type from dba_objects where=20 object_name=3D'TAX_CATEGORY_VALUE'; =20 OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------=20 ------------------ PUBLIC TAX_CATEGORY_VALUE SYNONYM HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE HIBM_TAXONOMY TAX_CATEGORY_VALUE UNDEFINED =20 SQL> select query from dba_mviews where MVIEW_NAME=3D'TAX_CATEGORY_VALUE'; =20 QUERY ---------------------------------------------------------------------------= ----- SELECT "TAX_CATEGORY_VALUE"."ID" "ID","TAX_CATEGORY_VALUE"."NAME_ID"=20 "NAME_ID"," TAX_CATEGORY_VALUE"."VALUE" "VALUE","TAX_CATEGORY_VALUE"."IS_ACTIVE"=20 "IS_ACTIVE" FROM "TAX_CATEGORY_VALUE"@Q2HIBM.US.HSBC.COM "TAX_CATEGORY_VALUE" =20 =20 SQL> desc TAX_CATEGORY_VALUE Name Null? Type ----------------------------------------- --------=20 ---------------------------- ID NOT NULL NUMBER(10) NAME_ID NOT NULL NUMBER(10) VALUE NOT NULL VARCHAR2(100) IS_ACTIVE NUMBER(38) ALIAS NUMBER(10) =20 SQL>=20 >From 9i: =3D=3D=3D=3D=3D=3D=3D SQL> select owner,object_name,object_type from dba_objects where=20 object_name=3D'TAX_CATEGORY_VALUE'; =20 OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------=20 ------------------ PUBLIC TAX_CATEGORY_VALUE SYNONYM HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE HIBM_TAXONOMY TAX_CATEGORY_VALUE MATERIALIZED VIEW =20 SQL>=20 SQL> desc TAX_CATEGORY_VALUE Name Null? Type ----------------------------------------- --------=20 ---------------------------- ID NOT NULL NUMBER(10) NAME_ID NOT NULL NUMBER(10) VALUE NOT NULL VARCHAR2(100) IS_ACTIVE NUMBER(38) ------=_Part_1092_21068614.1125206023564 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Could someone tell me h= ow "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:
=3D=3D=3D=3D=3D=3D=3D=3D
SQL> select owner,object_name,object_type from dba_objects where object_= name=3D'TAX_CATEGORY_VALUE';

 
OWNER   =             &nb= sp;          OBJECT_NAME          &nbs= p;         OBJECT_TYPE
-----------------------= ------- ------------------------------ ------------------
PUBLIC   = ;            &n= bsp;         TAX_CATEGORY_VALUE         &nb= sp;   SYNONYM
HIBM_TAXONOMY &nbs= p;            &= nbsp;   TAX_CATEGORY_VALUE         &nb= sp;   TABLE
HIBM_TAXONOMY &nbs= p;            &= nbsp;   TAX_CATEGORY_VALUE         &nb= sp;   UNDEFINED
 
SQL> select query fr= om dba_mviews where MVIEW_NAME=3D'TAX_CATEGORY_VALUE';
 
QUERY
-----------------------= ---------------------------------------------------------
SELECT "TAX_CATEGO= RY_VALUE"."ID" "ID","TAX_CATEGORY_VALUE"= ."NAME_ID" "NAME_ID","
TAX_CATEGORY_VALUE"= ;."VALUE" "VALUE","TAX_CATEGORY_VALUE"."= IS_ACTIVE" "IS_ACTIVE"
 FROM "TAX_CA= TEGORY_VALUE"@Q2HIBM.US.HSBC.COM= "TAX_CATEGORY_VALUE"
 
 
SQL>  desc TAX_= CATEGORY_VALUE
 Name  &= nbsp;           &nbs= p;            &= nbsp;          Null?    Type
 -----------------= ------------------------ -------- ----------------------------
 ID  &nb= sp;            =             &nb= sp;            NOT NULL NUMBER(10)
 NAME_ID &nbs= p;            &= nbsp;           &nbs= p;        NOT NULL NUMBER(10)
 VALUE  =             &nb= sp;            =           NOT NULL VARCHAR2(100)
 IS_ACTIVE &n= bsp;            = ;            &n= bsp;            = ;   NUMBER(38)
 ALIAS  =             &nb= sp;            =             &nb= sp;      NUMBER(10)
 
SQL>


From 9i:
=3D=3D=3D=3D=3D=3D=3D
SQL>  select ow= ner,object_name,object_type from dba_objects where object_name=3D'TAX_CATEG= ORY_VALUE';
 
OWNER   =             &nb= sp;          OBJECT_NAME          &nbs= p;         OBJECT_TYPE
-----------------------= ------- ------------------------------ ------------------
PUBLIC   = ;            &n= bsp;         TAX_CATEGORY_VALUE         &nb= sp;   SYNONYM
HIBM_TAXONOMY &nbs= p;            &= nbsp;   TAX_CATEGORY_VALUE         &nb= sp;   TABLE
HIBM_TAXONOMY &nbs= p;            &= nbsp;   TAX_CATEGORY_VALUE         &nb= sp;   MATERIALIZED VIEW
 
SQL>
SQL> desc TAX_CATEGO= RY_VALUE
 Name  &= nbsp;           &nbs= p;            &= nbsp;          Null?    Type
 -----------------= ------------------------ -------- ----------------------------
 ID  &nb= sp;            =             &nb= sp;            NOT NULL NUMBER(10)
 NAME_ID &nbs= p;            &= nbsp;           &nbs= p;        NOT NULL NUMBER(10)
 VALUE  =             &nb= sp;            =           NOT NULL VARCHAR2(100)
 IS_ACTIVE &n= bsp;            = ;            &n= bsp;            = ;   NUMBER(38)
 

------=_Part_1092_21068614.1125206023564-- -- http://www.freelists.org/webpage/oracle-l