I run into this problemevery now and then and I keep forgetting how to solve it. Here it is:
If you have a block based on a table, and the primary key is automatically filled with a database trigger, do not set “Enforce primary key” to YES for the block.
It’s been a while but here is a pretty bug, which occurs even in the newest Oracle Database versions:
I tried to create six materialized views on views in a remote database through a database link. (for whomever is interested: the remote database used the CDM Ruleframe with VAPIs so I was asked to select from the views instead of the tables).
Four of the six compiled nicely. The other two gave me this error (on 10.1.0.2 and 10.2.0.1):
ORA-00942: table or view does not exist
ORA-06512: at “SYS.DBMS_SNAPSHOT_UTL”, line 960
ORA-06512: at line 1
and when I tried on another database (10.2.0.3) the error turned out to be an ora-00600!
I solved this by making the materialized view complex, i.e. just putting select * from () around it.
The bug on metalink for this is here