Toad compare - wrong trigger code

Toad for Oracle & DevOps Toolkit
21

Hi Christophe,

Can you do a generate schema script on the schemas being compared and email it to me so I can try to reproduce it? john.dorlon@quest.com

-John

22

What option in Toad do you mean with "generate schema script"? (the create script via Users does not include packages and triggers)

23

From main menu:
Database -> Export -> Generate Schema Script.

24

duo_dao.source.sql
duo_dao.target.sql

25

Hi Christophe,

I am not sure yet why this is happening, but it's interesting that the trigger with the problem is the first one alphabetically. Some questions and comments:

  1. When this happens, is it always the first trigger (alphabetically) in your schema?
  2. Does this only happen with triggers? Never packages or anything else?
  3. The only difference that I'm seeing is the case of the text in the DESCRIPTION column of DBA_TRIGGERS. This seems pretty unimportant because that's not the trigger code, just some metadata from your CREATE TRIGGER statement. I could add an option to ignore these differences if you'd like. It might not solve the problem, but it would make the error a lot less frequent, if this is frequently the only difference in the triggers.

-John

26

Hi John

  1. I did some searches on previous compares. It is not always the 'first', but always near the first (alphabetically starting with A)
    Often(not always) another object exists in another schema exists with the same name (with other triggers on it), not sure if this is related.
  2. It only happens with triggers.
  3. I would be nice to have the option to ignore the "DBA_TRIGGERS.DESCRIPTION" column since it only contains derived data.

I use the .ini file option "Dont compare schema name in trigger desc", but ignoring it completely would be better.

Just a question (might be related):
How is the trigger text-generated in the output-file?
eg
CREATE OR REPLACE TRIGGER DUO_DAO.act_accounts_dio
instead of delete
ON DUO_DAO.ACT_ACCOUNTS_TV
for each row

--> This is the exact text as created on source. Do you use DBMS_METADATA.GET_DDL or custom method?

27

Hi Chrisophe,

I checked the SQL that we're running, including when using a complicated "not like" filter, as I see you are using. For me, it always includes "where owner = ...", so it shouldn't matter if the object exists in another schema.

I will add an option to at least ignore case and whitespace in DBA_TRIGGERS.DESCRIPTION. I am not sure that it can be safely ignored completely.

We create the trigger source using our own code, getting data from the values in DBA_TRIGGERS, and also sometimes DBA_SOURCE

I'll keep looking at this problem today.

-John