Type operation order /DMS-34058

jaspernygaard · November 7, 2018 6:26PM

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?

Alex B · February 19, 2019 12:11PM

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

andersharder · November 9, 2018 10:08AM

Just for completeness
The SCO script referred to by jaspernygaard lLooks like this:

<div>--
-- Script generated by Schema Compare for Oracle 4.0.11.536 on 07/11/2018 14:41:05
--
SET DEFINE OFF

CREATE OR REPLACE TYPE dcf_services."O_BESBEMAERKNING"                                          
AS OBJECT
 (id number,
  kode number,
  besnr number,
  dyreart number,
  bemaerkning varchar2(200),
  logonid varchar2(30),
  ajourtid date);
/

CREATE OR REPLACE TYPE dcf_services."TO_BESBEMAERKNING"                                          
   AS TABLE OF dcf_services.o_besbemaerkning;
/

ALTER FUNCTION dcf_services.fpipe_besbemaerkning COMPILE;

ALTER FUNCTION dcf_services.fpipe_besbemaerkning COMPILE;

DROP TYPE dcf_services.to_besbemaerkning;

</div><div></div>

jaspernygaard · November 27, 2018 9:49AM

@Eddie D
You guys had a change to look at this issue?

jaspernygaard · December 3, 2018 12:25PM

@Eddie D
Any input?

jaspernygaard · January 11, 2019 12:57PM

For reference, ticket created (128106).

mbruegel · February 8, 2019 10:13AM

I second the importance of resolving this issue.

jaspernygaard · February 8, 2019 10:16AM

@mbruegel, From the internal ticket they have created, they have reproduced the behavior and working on a fix. No eta, though.

Type operation order /DMS-34058

Best Answer

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk