Created Materialized View has COMPILE_STATE = ERROR if it selects from a SYS table (Doc ID 2230316.1)

Last updated on FEBRUARY 22, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.0 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.
The materialized view can be selected from and refreshed, but the COMPILE_STATUS is always ERROR

Symptoms

Customer created Materialized View which has COMPILATION_ERROR state.
Problem reproducible on 11.2.0.3, 11.2.0.4 and 12.1.0.2
There are no errors during MVIEW creation,  and the mview refreshes without error.

CREATE materialized VIEW REFER_MV refresh complete ON demand
AS
WITH n AS
(SELECT 'xmlns:xs="http://www.xxxx.org/2001/XMLSchema"'NS FROM dual
) ,
m AS
(SELECT t.extract('xs:attribute /@name',n.NS).getStringVal() NAME ,
t.extract('xs:attribute /@type',n.NS).getStringVal() TP ,
xmlType(trim(t.COLUMN_VALUE)) XMLT
FROM n ,
ALL_XML_SCHEMAS s ,

TABLE(XMLSequence(s.schema.extract('/xs:schema/xs:complexType/xs:attribute',n.
NS))) t
WHERE s.schema_url = 'bor_gid_c101_v1.0.0.xsd'
AND s.owner = 'OD'
)
SELECT m.NAME REFER ,
t.extract('xs:enumeration /@value',n.NS).getNumberVal() CODE ,

extractValue(t.extract('xs:enumeration',n.NS),'xs:enumeration/xs:annotation/xs
:documentation',n.NS) NAME
FROM n ,
m ,

TABLE(XMLSequence(extract(m.XMLT,'xs:attribute/xs:simpleType/xs:restriction/xs
:enumeration',n.NS))) t
WHERE m.TP IS NULL;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms