3

I have a table xml_documents with two columns: a document_id column (primary key) and an xml column with some XML data, which is a schema-less XMLType. I can create a materialized view with just the document_id with:

create materialized view mv refresh fast on commit as 
select document_id
from xml_documents

This works fine, but isn't very useful. As you might expect, I'd like the materialized view to extract data from the XML, and for this I use extractValue(). I am trying the following:

create materialized view mv refresh fast on commit as 
select document_id, extractValue(xml, '/my/gaga') gaga
from xml_documents

This fails with:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

How should I go about to create a fast refresh on commit materialized view that extract values from XML?

avernet
  • 30,895
  • 44
  • 126
  • 163

1 Answers1

2

Your XMLType is (probably) stored as a CLOB. Find the hidden column with a query like this:

select * from user_tab_cols where table_name = 'XML_DOCUMENTS';

Then create a function to convert a CLOB into an XMLType, and extract the value. Note that the "deterministic" keyword is necessary, although I'm not sure why. Passing data back and forth between SQL and PL/SQL will be slow, but if you're using a materialized view things are probably already slow. 

create or replace function extract_from_clob(p_xml in clob) return varchar2 deterministic
  is
begin
    return XMLType(p_xml).extract('/my/gaga/text()').getStringVal();
end;
/

Then drop and create your materialized view with the system column passed into the function:

create materialized view mv refresh fast on commit as 
select document_id, extract_from_clob(SYS_NC00003$) gaga
from xml_documents;

I'm unsure about using a system-generated hidden column. It works, but doesn't seem like a really good idea. At the very least it will make it difficult to create the object on different systems - you'll need to find the new column name each time.

It seems weird that XMLTypes don't work when LOBs work fine. I can't find any documentation about this; I'm not sure if it's a bug, an unimplemented feature, or if there's some magic setting that will make it work. If no one else can provide a better answer, it might be worth checking with Oracle support before you use the above approach.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Wow, thank you for this amazing answer. I think we're getting close, but when I run the `create materialized view…`, I get a `SQL Error: ORA-30625: method dispatch on NULL SELF argument is disallowed`, and I am not sure how to get around it. Do you have any idea? (The `xml` column in the original table is defined as `xmltype not null`, but this might be unrelated.) – avernet Apr 09 '11 at 06:05
  • How exactly did you create your table and materialized view log? Here are the commands I used: `create table xml_documents(document_id number primary key, xml xmltype not null);` and `create materialized view log on xml_documents with primary key;`. This worked for me on 10.2.0.1.0 and 11.2.0.1.0. – Jon Heller Apr 09 '11 at 16:36
  • thanks for the follow-up. I finally found what the problem was: `/my/gaga` didn't exist for some of the rows. So I imagine that the `extract()` was returning `null`, hence applying `getStringVal()` was leading to an error, and Oracle didn't let me create the materialized view. To get around this, I modified your `extract_from_clob()` to test avoid this problem. I also added the XPath expression as a parameter, which makes it usable as-is. – avernet Apr 12 '11 at 18:26