Red Gate forums :: View topic - Can you cause schema names to be ignored?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Schema Compare for Oracle
Schema Compare for Oracle forum

Can you cause schema names to be ignored?

Search in Schema Compare for Oracle forum
Post new topic   Reply to topic
Jump to:  
Author Message
rspooner



Joined: 24 Sep 2010
Posts: 1
Location: San Diego, CA

PostPosted: Fri Sep 24, 2010 9:36 pm    Post subject: Can you cause schema names to be ignored? Reply with quote

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!

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 in advance!
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Tue Sep 28, 2010 2:42 pm    Post subject: Reply with quote

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

Quote:
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.

Quote:
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
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Fri Oct 22, 2010 1:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Fri Oct 29, 2010 1:45 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Wed Jan 05, 2011 1:38 pm    Post subject: Reply with quote

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-development/schema-compare-for-oracle/

Thanks, Tom

Tom Harris, Red Gate Software
Back to top
View user's profile Send private message
Arden



Joined: 18 Jan 2011
Posts: 1

PostPosted: Tue Jan 18, 2011 3:12 pm    Post subject: Reply with quote

We'd also like an option to remove the schema name from the deployment script. Is this currently possible?
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Tue Jan 18, 2011 4:40 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group