TYPE changes with /abortonwarnings switch

mbruegelmbruegel Posts: 43 Bronze 2
edited April 12, 2019 10:54AM in Schema Compare for Oracle
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

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi Martin ( @mbruegel ),

    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:
    Severity: Medium
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • mbruegelmbruegel Posts: 43 Bronze 2
    With version 5.2.5 the  Warning: High message appears to be suppressed -- however it is still identified in the exit code status on the command line (correctly so -- because of a  drop statement) .

    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
     
    --
    -- Script generated by Schema Compare for Oracle 5.2.5.1424 on 30/04/2019 15:47:06
    --
    SET DEFINE OFF

    DROP TYPE parameter_var_t;

    CREATE OR REPLACE TYPE PARAMETER_PAIR_OBJ_T FORCE 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;
    /


  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @mbruegel,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • mbruegelmbruegel Posts: 43 Bronze 2
    The FORCE appears to persist once the object has been modified w/ a FORCE command.

    That, however, doesn't impact the behavior of the abort on warnings.

    Even more so, if SCO isn't using FORCE then it follows a drop and re-create pattern for dependent collections.

    Since an object is being dropped, the ABORT on WARNINGS should probably be HIGH-- and the commadline exit code identifies it as such, but it doesn't abort before the drop as expected.

    Version:  Schema Compare for Oracle version:5.2.5.1424: activated

    Command line:

    "C:\Program Files\Red Gate\Schema Compare for Oracle 5"\sco /source:mbruegel_parameter_Pair2.onp{MBRUEGEL} /target:mbruegel/<PWD>@&lt;DB_INSTANCE>{MBRUEGEL} /excludedependencies /exclude:additional /loglevel:verbose /report:ObjectDiff.html /reporttype:Simple /scriptfile:SCOUpdate.sql /verbose /abortonwarnings:high


    Echo w/ /abortonwarnings:high
    echo %errorlevel%
    63

    Echo w/o /abortonwarnings

    echo %errorlevel%
    61

    Source: is  Snapshot or Scripts folder  (no difference in behavior)
    Target Oracle DB:  11.2.0.4.0

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @mbruegel,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • mbruegelmbruegel Posts: 43 Bronze 2
    edited May 10, 2019 6:48PM
    Hi Alex,

    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)
  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @mbruegel ,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • mbruegelmbruegel Posts: 43 Bronze 2
    Hi Alex, I tested w/ version 5.3.1 and the abortonwarnings behavior appears to be resolved.

Sign In or Register to comment.