We're having problems controlling Materialized View definitions with Team Coding in Toad 13.3.0.181.
When a materialized view is changed -- because its query has changed for example -- it's necessary to DROP and CREATE it. We create the replacement with the same name and to all intents and purposes it's a new version.
However, checking in yields a constraint error
Error: ORA-02291: integrity constraint (TC_ADMIN.TCX_OBJHIST_OBJ_PROJ_FK) violated - parent key not found ORA-06512: at line 1
probably because the object ID has changed due to the DROP/CREATE. We're trying to check in something which is not the same object as was checked out. Even subsequent checkings-out can error in the same way, although I'm still investigating exactly what's going on there.
What is the correct way of maintaining the version history of a materialized view?
A workaround for the issue is to allow Team Coding to analyse any script and suggest what needs to be checked out — I've found that's usually just the indexes. Materialized views aren't flagged as needing to be checked out. Check everything out which is required, run the script and then check out the materialized views. Team Coding will signal a difference between the database and the VCS (because you've just altered the database), so choose the database version and then immediately check it back in and force a revision in the VCS.
While this works, and provides a version history, it rather negates the advantages of signalling to others that a particular materialized view is subject to change! Even if it's checked out while changes are planned, in order to make those changes it needs to be checked in (or have its check-out undone) and then corrected afterwards.
There ought to be a better way, but I haven't found it yet.
Hi Andrew,
I can see the issue where trying to drop and create the materialized view via a script from within the editor via F5 won't prompt for a checkout / checkin. The main reason for this is because a checkout is not required to drop an object from the database. However, recreating it should probably create a checkin or add process in the VCS. We'll see if we can get that addressed in the next beta cycle.
In the meantime, I think the best solution for managing materialized views in Team Coding would be to do so from the Schema Browser. Since they need to be dropped and recreated, it's easy to execute those functions from within the Schema Browser and the status of those objects will be reflected in the Team Coding Manager. That would help avoid the errors you received. There's no need to check out the objects before you do the drop and recreate them -- Team Coding should handle that automatically. The downside to this solution is that a new revision may not be created automatically in the VCS. For that, you'll probably need to check the object out and check it back in again. I suspect this is probably caused by the same issue as in the editor example above and is due to the fact the object needs to be dropped and recreated, rather than just altered. We'll see if we can get this addressed as well in the next beta cycle.
Thanks for letting us know about this, and let us know if there's anything else we can do!
-John