DROP TYPE fails due to TABLE-of-type dependencies

piers7

We use Schema Compare for Oracle (3.1.5.1321) to as part of our dev workflow to generate rollforward and rollback scripts for our schema modifications (working against local Oracle 11g XE instance).

Often we find when we come to execute the rollback scripts that the DROP statements are in the wrong order, and attempt to drop TYPES that are OBJECTS prior to TYPES that are TABLES of the type of the object.

eg:

rollforward script (generated by SCO compare between local DB and previous stored snapshot file):
CREATE OR REPLACE TYPE schema.O_something as OBJECT (...)
CREATE OR REPLACE TYPE schema.T_something as TABLE of O_something;

rollback script (generated by SCO compare between previous stored snapshot file and local DB):
DROP TYPE schema.o_something; -- fails because of dependent table
DROP TYPE schema.t_something;

Is this a known issue?

NB: It doesn't do this all the time, so I suspect the ordering is just non-deterministic.

Eddie D

Hi, thank you for your forum post.

The deployment scripts created are generated in dependency order. In my experience when the deployment fails in the manner you have described, it is either due to the dependency not being detected or in some rare cases, there is a circular dependency (object_A depends on object_B, object_B depends on object_C and object_C depends on object_A).

A support ticket has been created for you, ticket reference #80415.

Would it be possible for you email support@red-gate.com or update the call via the support portal with details of the TYPES that are objects and TYPES that are tables for me to test against?

Many Thanks
Eddie