Type operation order /DMS-34058

jaspernygaardjaspernygaard Posts: 38 Bronze 1
edited January 11, 2019 12:23PM in Schema Compare for Oracle

It seems SCO order TYPE operations wrongly in deployment scripts

I have used SCO to create a deployment script for 2 types.

See attached file SCO_depend_otype_depl_example.sql

One of the types is dependent on the other type (in the example: type "TO_BESBEMAERKNING" is 'table of' type "O_BESBEMAERKNING" - this is clear in the 'CREATE ...' statements created)

The "O_BESBEMAERKNING"  changes cannot be deployed with the "TO_BESBEMAERKNING" type in place. In some ways SCO recognizes this as it creates the DROP statement necessary. 

The problem is that in the generated script file the statements are ordered wrongly so the 'DROP' is last.

If just the 'DROP ...' statement of the dependent object was before the 'CREATE OR REPLACE ' of the type it was dependent on, the script would work a dream. 

This seems like a simple script output ordering of statements that have gone wrong.

The script generation does warn:

Severity: Medium
Object: dcf_services.o_besbemaerkning
Title: AlterType
In some situations, it is not possible to deploy changes to a type object. If the type object is used as a column in another table, the deployment script will fail. If the type object is used by an object table, or object view, the script will fail. We recommend you investigate how your type is used before running the deployment script.
----------------------------------------------------

Which is correct but not really relevant?. The first part " If the type object is used as a column in another table, the deployment script will fail" is not relevant in this case. The other part " If the type object is used by an object table, or object view, the script will fail." is a bit unclear to me. The table is only used in another type not in (object) tables or views (also it is used in stored code (packages/functions/procedures) but that is only a temporary problem if the deployment is done correctly )

 

Again: It's commendable that SCO recognized that the dependent type needs to be dropped and recreated. It is unfortunate that it does not do it in the right order.

 

I notice that if I try to deploy only for the "O_BESBEMAERKNING" only I get exactly the same script generated. In the process it suggest to 'Deploy all affected objects (Recommended)" and list "TO_BESBEMAERKNING". Again it is commendable that it recognized the dependency and recognizes the steps necessary to take to handle this, it is though unfortunate it generates the steps in the wrong order in the resultant script file.

Furthermore there are a few code objects recompilations in the scripts. These are not really relevant to the problem at hand (They are not strictly necessary as the objects would autorecompile on first use, but if retained should be after all statements on the underlying objects).

Any input on how to deal with this?

Tagged:

Best Answer

  • Alex BAlex B Posts: 779 Diamond 1
    Accepted Answer
    Hi all,

    @jaspernygaard has confirmed that the new version 5.2.4 has fixed this issue.  It was specifically occurring when there was a function using a type that was based on another type (my initial attempts were just one type based on another and that worked).

    If you have not already, please update to version 5.2.4 to correct this issue!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

Sign In or Register to comment.