Can you cause schema names to be ignored?

rspooner

Is there an option where the schema name (and any associated double quotes on the schema name and the object name) can be ignored if the schema name matches the name of the schema you entered in the Source/Target dialog?

I am using Schema Compare to compare the contents of a number of schemas, one at a time, against a "master" schema. Some of our PL/SQL contains schema names. When I compare a SOURCE schema against a TARGET schema, Schema Compare flags such lines as differences, which is not what I was hoping for. As an example, I have:

In SOURCE:

CREATE TRIGGER "SOURCE".TRIGGER1 AFTER DELETE....

In TARGET:

CREATE TRIGGER "TARGET".TRIGGER1 AFTER DELETE....

While I accept these two lines are lexically different, each schema would react identically when the PL/SQL is executed in each schema, and I don't want Schema Compare to flag such differences. There are probably over 3,000 such lines flagged by Schema Compare per comparison in my environment, which is rather a lot to go through and eyeball!

Developing this a stage further:

If there was another schema name specified which did not match the schema under scrutiny:

In SOURCE:

CREATE TRIGGER "SOURCE".TRIGGER2 AFTER DELETE....

In TARGET:

CREATE TRIGGER "MASTER".TRIGGER2 AFTER DELETE....

I would want this flagged as a difference.

Also:

If there a schema name was missing, but the schema name which was present matched the name of the schema under scrutiny, perhaps there should be a "sub-option" to determine whether or not a difference should be flagged:

In SOURCE:

CREATE TRIGGER "SOURCE".TRIGGER3 AFTER DELETE....

In TARGET:

CREATE TRIGGER TRIGGER3 AFTER DELETE....

But the following is definitely a difference, as you are specifying a schema name on the object which is not the same as the schema under scrutiny.

In SOURCE:

CREATE TRIGGER "MASTER".TRIGGER3 AFTER DELETE....

In TARGET:

CREATE TRIGGER TRIGGER3 AFTER DELETE....

Thanks in advance!

neil.anderson

Hi rspooner, thanks for trying out Schema Compare for Oracle.

Is there an option where the schema name (and any associated double quotes on the schema name and the object name) can be ignored if the schema name matches the name of the schema you entered in the Source/Target dialog?

I am using Schema Compare to compare the contents of a number of schemas, one at a time, against a "master" schema. Some of our PL/SQL contains schema names. When I compare a SOURCE schema against a TARGET schema, Schema Compare flags such lines as differences, which is not what I was hoping for. As an example, I have:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER1 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER "TARGET".TRIGGER1 AFTER DELETE....


While I accept these two lines are lexically different, each schema would react identically when the PL/SQL is executed in each schema, and I don't want Schema Compare to flag such differences. There are probably over 3,000 such lines flagged by Schema Compare per comparison in my environment, which is rather a lot to go through and eyeball!

My understanding is that Schema Compare already does this by default at the moment. PL/SQL that differs just by the schema name is not considered different and appears as an identical object, but if you select it, the SQL Differences pane will still highlight any textual differences in the creation script.

If you are seeing objects which are flagged as different and the only difference appears to be the schema name then it would be useful if you could provide us with further information so we can investigate.

Developing this a stage further:

If there was another schema name specified which did not match the schema under scrutiny:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER2 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER "MASTER".TRIGGER2 AFTER DELETE....


I would want this flagged as a difference.

Also:

If there a schema name was missing, but the schema name which was present matched the name of the schema under scrutiny, perhaps there should be a "sub-option" to determine whether or not a difference should be flagged:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER3 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER TRIGGER3 AFTER DELETE....


But the following is definitely a difference, as you are specifying a schema name on the object which is not the same as the schema under scrutiny.

In SOURCE:
Code:
CREATE TRIGGER "MASTER".TRIGGER3 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER TRIGGER3 AFTER DELETE....

Thanks for your suggestions. We will consider this as a feature request for future releases of the tool.

Regards,
Neil Anderson

Tom Harris

Hi there,

we are implementing this for the next update for Schema Compare for Oracle. Once we have a stable build we'll send you over a copy to test out

Many thanks for your detailed input

-Tom

Tom Harris

Hi,

good news! We do now have a build of Schema Compare for Oracle that ignores the schema name when visually 'diffing' objects. Please email oraclesupport@red-gate.com and we'll send over the details

Thanks, Tom

Tom Harris

Hi there,

this feature is now part of Schema Compare for Oracle v1.5. You can download from http://www.red-gate.com/products/oracle ... or-oracle/

Thanks, Tom

Tom Harris, Red Gate Software

Arden

We'd also like an option to remove the schema name from the deployment script. Is this currently possible?

Tom Harris

Hi there,

many thanks for your feedback. I'm afraid that this is not possible in the current version. This enhancement has already been requested for the next version of Schema Compare. I have added your 'vote' to the request. I can't make any promises at this stage about what will be included in the next version though.

Kind regards, Tom

Tom Harris - Red Gate Software