Schema compare bug on triggers?

Toad for Oracle - Beta
1

Toad 12.6.0.40 x64
Windows 7 x64 SP1
Oracle client 11.2.0.3 x64
Oracle database 11.2.0.3 (one on Win one on Lin, according compared schema

When I run schema compare against two schemas (on one Win Db and one on Lin db, same versions), in trigger definition it shows manydifferences:

SC_1.png
SC_1.png584×966 57.1 KB

when you ope any of these "unsynchronized" trigger, it shows no differences.

sc_2.png
sc_2.png1610×1050 64 KB

Even when you run synchro script, some triigers vanished but many of them still remains.

Seems that has something with NLS, so I'm showing them here.

here are NLS parameters on schema which is source for compare (db on Windows):

PARAMETER VALUE

NLS_LANGUAGE CROATIAN
NLS_TERRITORY CROATIA
NLS_CURRENCY kn
NLS_ISO_CURRENCY CROATIA
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE CROATIAN
NLS_SORT CROATIAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY kn
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

PARAMETER VALUE

NLS_LANGUAGE CROATIAN
NLS_TERRITORY CROATIA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

PARAMETER VALUE

NLS_LANGUAGE CROATIAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY CROATIA
NLS_CURRENCY kn
NLS_ISO_CURRENCY CROATIA
NLS_NUMERIC_CHARACTERS ,.
NLS_CHARACTERSET EE8MSWIN1250
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE CROATIAN
NLS_SORT CROATIAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY kn
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.3.0

20 rows selected.

Here are NLS of destination schema-which is compared with source one (-db on Linux)
PARAMETER VALUE

NLS_LANGUAGE CROATIAN
NLS_TERRITORY CROATIA
NLS_CURRENCY kn
NLS_ISO_CURRENCY CROATIA
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE CROATIAN
NLS_SORT CROATIAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY kn
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

PARAMETER VALUE

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT &

2

This is not related to NLS. When it says “Description”, it is referring to the “Description” column of dba_triggers. In the Schema Compare options, look on the “Object Type Specific Options” tab. For Triggers, it could be that the 2nd option - “Compare schema name in description” option is checked, and that is causing the difference (even though it may not be a real difference).

3

Hi,

John you are right. That option is checked in mine case. Please look at the example of exposed trigger from both sides, description column:

**MR_APP.**apex$team_dev_files_biu
before insert or update ON APEX$TEAM_DEV_FILES
for each row

apex$team_dev_files_biu
before insert or update ON APEX$TEAM_DEV_FILES
for each row

I do not want to expose why is placing schema name in trigger definition very dangerous thing but this problem (schema name in trigger definition) should be removed from definition and may be the only cause of running compare tool, so this lead me to say that something is still wrong then elsewhere.

In “File Difference Viewer” (second picture you see in mine post) this is not shown…I mean there is no schema name but both triggers seems to be identical-they are really not identical in any mean!? And this is somehow misleading from mine point of view.

For the end, the best of all, synchro script is correctly generated (with or without schema, regardless what is master compare schema) and this OK.

So mine standing is to show pure vanilla compare, where such a difference will be shown.

Brg
Damir

4

The schema name is present in the DESCRIPTION column of DBA_TRIGGERS if the schema name was included when you created the trigger. If you create the trigger without specifying the schema, then it will not be present in the description.

So your option to ignore this difference is to uncheck “compare schema name in description”.

Of course, you can always recreate the triggers without the schema name if you don’t want to have them there.

Unfortunately, it’s not always practical to just show the raw trigger text in the side by side viewer, because there are so many options of what to compare (ignore case, whitespace, format before comparing, include/exclude schema, etc). If I just showed the raw text, then the side by side compare would show differences that you have chosen to ignore, and the “real” difference would be buried within all of that. So the side by side viewer shows the script that it would create for the trigger based on your options.

I’ll see if I can make some improvements in the “Differs by” text to make it more clear what the difference is in this case.