ORA-12026: invalid filter column detected

Cause:

Primary key columns are not allowed in rowid clause, if primary key is specified in the syntax explicitly or the column really does not exist due to drop.

kIsH@STAX<>!oerr ora 12026
12026, 0000, "invalid filter column detected"
// *Cause:  One or more of the specified filter columns did not exist or was a
//          primary key column or a primary key based object identifier.
// *Action: Ensure that all specified filter columns exist in the master table
//          and ensure that primary key columns or primary key based object
//          identifiers are not included in the list of filter columns.

Create mview fails with the below error

kIsH@STAX<>create materialized view log on xtbl
               with primary key,
                    rowid(XID,XDATE)
                         including new values;  2    3    4
create materialized view log on xtbl
*
ERROR at line 1:
ORA-12026: invalid filter column detected

Solution:

Check the definition of the columns and remove the primary key column in the rowid clause

kIsH@STAX<>desc xtbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(100)
 XID                                       NOT NULL NUMBER(20)
 CITY                                               VARCHAR2(100)
 COUNTRY                                            VARCHAR2(100)
 DEPARTMENT                                         VARCHAR2(100)
 ZIPCODE                                            NUMBER(20)
 XDATE                                     NOT NULL DATE
 SALARY                                             NUMBER(38)

kIsH@STAX<>create materialized view log on xtbl
  2               with primary key,
  3                    rowid(XDATE)
  4                         including new values;

Materialized view log created.

Leave a Reply