TYPE changes with /abortonwarnings switch
mbruegel
Posts: 43 Bronze 2
We're running SCO version 5.2.4.1278 from the command line and observed some odd behavior when changing an object type definition (referenced by a collection type) when combined w/ the /abortonwatnings:high switch.
Scenario:
- changing the attribute definition on object used in a collection
- execute a SCO deployment w/ /abortonwatnings:high
- expected behavior:
-- abort / error prior to drop of collection type and exit w/ code 63
-- underlying object type is not updated
- observed behavior:
-- object type attributes are updated
-- SCO exits w/ 63 (because of the drop of the collection type)
-- after the SCO failure, when we look at the object on the target DB the datatypes of the object has been updated despite the abort on warnings failure
-- note from the captured UPDATE DDL the actual DROP appears to be executed before the type update
/* UPDATE DDL generated from SCO: */
--
-- Script generated by Schema Compare for Oracle 5.2.4.1278 on 10/04/2019 17:24:35
--
SET DEFINE OFF
DROP TYPE parameter_var_t;
CREATE OR REPLACE TYPE PARAMETER_PAIR_OBJ_T AS OBJECT
(param_name VARCHAR2(100),
param_value VARCHAR2(1000));
/
CREATE OR REPLACE TYPE PARAMETER_VAR_T AS VARRAY(10) OF parameter_pair_obj_t;
/
/* Original Object Definition */
CREATE OR REPLACE TYPE PARAMETER_PAIR_OBJ_T AS OBJECT
(param_name VARCHAR2(50),
param_value VARCHAR2(500));
/
CREATE OR REPLACE TYPE PARAMETER_VAR_T AS VARRAY(10) OF parameter_pair_obj_t;
/
/* Target / Updated Definition */
CREATE OR REPLACE TYPE PARAMETER_PAIR_OBJ_T AS OBJECT
(param_name VARCHAR2(100),
param_value VARCHAR2(1000));
/
CREATE OR REPLACE TYPE PARAMETER_VAR_T AS VARRAY(10) OF parameter_pair_obj_t;
/
Any explanation for this behavior?
- martin
Tagged:
Answers
What High warning are you expecting this to cause so that it would trigger the /abortonwarnings:high switch?
When run from the UI the warning that occurs for this deployment script is a Medium warning:
Object: parameter_pair_obj_t
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.
The list of warnings can be found on this page and having a look through the existing list I don't see a high warning that should trigger in this case.
Kind regards,
Alex
Have you visited our Help Center?
Run the sco.exe command line (deployment is not necessary)
- w/ the /abortonwarnings:high switch you get an exit code 63
- w/o the switch you get the exit code: 61
This appears to be a bad / incomplete fix.
- I see that FORCE argument has been added to the create or replace of the type (it wasn't there in 5.2.4 -- see generated SQL above)
- The drop of the associated collection type is still there although it doesn't need to be (because of the FORCE option added to the CREATE OR REPLACE TYPE)
- the returned exit code (63) is technically correct, because of the drop statement
(the WARNING HIGH has been removed -- suspect because of the addition of the FORCE, but the associated DROP is still there -- so this is incomplete and inconsistent)
Here is the update SQL generated by SCO which clearly still has the DROP statement (-- and therefore should be a High warning).
- fix would appear to be: remove the associated collection drop (unnecessary because of the use of FORCE)
- you may need to do some investigation for the appropriate DDL sequence if the object or collection type is referenced by a persisted column -- FORCE can't be used under that scenario
What is the command line command you are using? Please ensure you obfuscate the user/password. And what source and target types are you using (and Oracle db versions)?
I'm comparing from a scripts folder to a live database, with the /abortonwarnings:high and creating a scriptfile to check what would be run and when I run echo %errorlevel% I get 61 and the script file does not have the FORCE keyword in 5.2.5 - it just shows the differences in PARAMETER_PAIR_OBJ_T and drops then recreates the parameter_var_t.
Kind regards,
Alex
Have you visited our Help Center?
Righto I see what is happening here.
So in the application, it's mistakenly always setting the exit code to be 63 when there is the combination of _any_ (Medium OR High warning) AND the abortonwarnings is set.
So in your case here, there is a Medium warning and the abortonwarnings is set to high, which is matching the criteria to proceed into the inner portion and therein it sets the exit code to be 63 regardless of whether there is a warning that matches the threshold to abort the deployment.
I have created a pull request with the team to fix this and I will update you when this is available in a release.
There is a separate question/issue that from your perspective dropping a type should be a high warning, whereas we indicate that it is a medium severity warning as we put it in the category of "Script may fail" because the type may fail to be created (see this page for list of deployment warnings):
Warning: Medium: Type object cannot be deployed
Object: <schema>.<object name>
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.
This has been like this since at least v3 so it's not likely to change, but I have passed the feedback on to the team.
I will keep you updated on the fix and any replies!
Kind regards,
Alex
Have you visited our Help Center?
My main points about the type change are as follows:
1. dropping the associated collection type could be a high(er)-risk activity -- as it is an implicit result of an object change not an explicit one made to the collection type
-- i.e. even if the object is re-created I may be in danger of losing any grants on the object, if I do not include grants in my comparison actions
2. the implicit drop / recreate of the associated collection object appears unnecessary
-- why not use the FORCE option? (Note: it does persist once used -- but have you identified a use case where that is undesirable?)
-- or at least check if the object DDL and *if* it contains the FORCE keyword don't drop the owning collection object to begin with
Also note have an open support case that exhibits a similar behavior:
(63 exit code is set when a tablespace for a table is changed)
As you will likely hear shortly from the ticket as well, the fix I've done should be included in the release that came out today (v 5.3.1).
Please try this version and let me know if you still have any issues with that aspect of things.
Regarding your view on the severity of the action, I've passed your feedback on to the team, but it is something that will take a lot of consideration as it has been as it is for some time and may be expected to work as it does (rightly or wrongly).
Kind regards,
Alex
Have you visited our Help Center?