Triggers and WHEN clauses /DMS-34056

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

SCO has a problem with consistently extracting WHEN clauses of triggers.

E.g. for trigger h0915.DPFW_LOGHANDLER_ID_TRG

SCO claims the triggers are different. In the visual tool displayed as:


Script left hand side (my highlighting)

 

-- PL/SQL Text
CREATE OR REPLACE TRIGGER h0915.DPFW_LOGHANDLER_ID_TRG
 BEFORE INSERT
 ON h0915.DPFW_LOGHANDLER
 FOR EACH ROW
 WHEN (NEW.id IS NULL) BEGIN
 SELECT dpfw_loghandler_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

 

Scripts right hand side

 

-- PL/SQL Text
CREATE OR REPLACE TRIGGER h0915.DPFW_LOGHANDLER_ID_TRG
 BEFORE INSERT
 ON h0915.DPFW_LOGHANDLER
 FOR EACH ROW
 BEGIN
 SELECT dpfw_loghandler_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

 

On the DB for RHS I execute (with same user as executing SCO exctract)

 

select dbms_metadata.get_ddl('TRIGGER', 'DPFW_LOGHANDLER_ID_TRG', 'H0915') from dual

And gets:

CREATE OR REPLACE TRIGGER "H0915"."DPFW_LOGHANDLER_ID_TRG" 
 BEFORE INSERT
 ON DPFW_LOGHANDLER
 FOR EACH ROW
 WHEN (NEW.id IS NULL) BEGIN
 SELECT dpfw_loghandler_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
ALTER TRIGGER "H0915"."DPFW_LOGHANDLER_ID_TRG" ENABLE

Which demonstrates the WHEN clause is present at the DB.

(Also: DB LHS is a copy by expdp/impdp of RHS)

SCO has failed extracting the WHEN clause of the trigger. But this is not a general omission as can be seen in that LHS has extracted WHEN clause.

LHS user is DBA, RHS is not, but it is not a simple permission issue as RHS user can see the when clause in data dictionary. At least via dbms_metadata.


Any workaround for this?

Tagged:
· Share on Twitter

Best Answer

  • jaspernygaardjaspernygaard Posts: 38 Bronze 1
    Answer ✓
    For reference, ticket created (125727). However we came to conclusion that this behavior is due to our current Oracle version has been upgraded from v9. Src and target db will be aligned upon the first deployment with SCO.
    · Share on Twitter

Answers

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi, thank you for your forum post.

    Using your DDL has a guide and V5.0.0.916 of SCO, sadly I have been unable to reproduce the problem you have reported, like yourself using different user names.

    A support ticket has been created for you and I will send a private message with the ticket details.  Would it be possible for you to increase the minimum logging level to verbose (as per this help document), repeat the comparison and when the results display highlighting the problem, update the support ticket with a copy of the log files generated?

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • jaspernygaardjaspernygaard Posts: 38 Bronze 1
    I'll try with the newest version and attached the log file. 
    · Share on Twitter
Sign In or Register to comment.