Materialized views with REFRESH ON COMMIT option

Hi

I have been attempting to created a materialized view that I need to refresh when 1 of the underlying tables is updated. The view is as follows:

CREATE MATERIALIZED VIEW DAT_V_QUERIES
BUILD IMMEDIATE
REFRESH ON COMMIT
AS

	SELECT TAB4.ID COL1,
	TAB4.SAMPLEID COL2,
	GET_DATA_ITEM_LIST(TAB4.ID) COL3,
	TAB4.SEQUENCE COL4,
	TAB4.STATUS COL5,
	DECODE(TAB4.STATUS, 0, 'Closed', 1, 'Open', 2, 'New', NULL) COL6,
	TAB4.TITLE COL7,
	TAB4.USERRAISEDBYID COL8,
	TAB1.USERNAME COL9,
	TAB1.FULLNAME COL10,
	TAB4.RAISEDDATETIME COL11,
	TAB4.USERCLOSEDBYID COL12,
	DECODE(TAB4.USERCLOSEDBYID, NULL, NULL, TAB5.USERNAME) COL12,
	DECODE(TAB4.USERCLOSEDBYID, NULL, NULL, TAB5.FULLNAME) COL13,
	TAB4.CLOSEDATETIME COL14,
	TAB2.ID COL15,
	TAB2.FIELDDESCRIPTION COL16,
	VW1.STUDYID COL17,
	VW1.STUDY COL18,
	VW1.PERIODID COL19,
	VW1.SUBJECTID COL20,
	VW1.VOLUNTEERID COL21,
	VW1.SAMPLEDATAID COL22,
	VW1.DATATYPE COL23,
	VW1.SAMPLETYPEID COL24,
	VW1.TYPE COL25,
	TAB4.EXTERNALCHECKID COL26,
	TAB4.IGNOREONIMPORT COL27
FROM 	TABLE1 TAB1,
 	TABLE2 TAB2,
	VIEW 1 VW1,
	TABLE4 TAB4 LEFT OUTER JOIN TABLE5 TAB5 ON TAB4.USERCLOSEDBYID =
TAB5.ID
WHERE 	VW1.ID = TAB4.SAMPLEID
 AND 	TAB4.USERRAISEDBYID = TAB1.ID
 AND 	TAB4.QUERYTYPEID = TAB2.ID;

When I attempt to create this view I get the error:

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

As far as I can tell the view matches the criteria for creating materialized views that can be refreshed ON COMMIT. The column TAB5.ID is the primary key so it is not the OUTER JOIN causing the problem.

Can anybody please help? This is the first time I have tried to create a materialized view, I have read the documentation but can only get the view to work using the ON DEMAND clause.

I do not wish the application to have to execute the DBMS_MVIEW.REFRESH package to refresh the view as this means anywhere where inserts, deletes, or updates are performed we will have to remember to include a call to the package. This cannot be done using a trigger either.

Any help/suggestions would be greatly appreciated. I am using Windows2000, Oracle 9.2.0.2.0.

Thanks in advance Received on Wed Oct 12 2005 - 10:57:49 CDT