XML materialized view

Posted by Roshan on Jul 06, 2021; 5:34am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/XML-materialized-view-tp9948.html

Oracle DB 12.1.0.2
Solaris 11.4

Hello Erman,

I would like to know if Oracle can capture DML on XML in redo logs. I am unable to perform fast refresh on below SQL

select WRKFLW_INSTANCE_ID_N,activityName,activityStatus,batchOnline,maxHoldTime,parentCount,parentSequence,sequenceNumber,timer,timerFlag,urlKeyCode,workAgent,lvl.id,lvl.resolutionTime,lvn.*
from WF_WORKFLOW,
xmltable(
  '/WorkFlow/Activity[node()]'
  passing WRKFLW_DATA_X
  columns
    activityName varchar2(100)  path '@activityName ',
    activityStatus varchar2(100) path '@activityStatus',
    batchOnline char(1) path '@batchOnline',
    maxHoldTime number(10) path '@maxHoldTime' ,
    parentCount number(2) path '@parentCount',
    parentSequence varchar2(15) path '@parentSequence',
    sequenceNumber varchar2(15) path '@sequenceNumber',
    timer number(2) path '@timer',
    timerFlag char(1) path '@timerFlag',
    urlKeyCode varchar2(2) path '@urlKeyCode',
    workAgent number(5) path '@workAgent',
    lvl xmltype path 'Owners/Level'
     
) wfl,
xmltable(
    'Level' passing lvl
    columns
        id number(10) path '@id ',
        resolutionTime number(10) path '@resolutionTime',
        lvn xmltype path 'Owner'
) lvl,
xmltable(
    'Owner' passing lvn
    columns
        defaultUser varchar(5),
        userGroup char(1) path '@userGroup',
        userGroupId number(5) path '@userGroupId'
        ) lvn

Grateful if you can advise.

Thanks,

Roshan