Compare schema improvement (suggestion)

Toad for Oracle - Beta
1

Hi,

Toad 12.1 x64 (but affected all versions).

Here is example of constraint improvement that might help a lot…
Many Oracle users write create table statement like:

create table t (
COL1 NUMBER NOT NULL,
COL2 VARCHAR2(10 BYTE) NOT NULL
);
where constrained is automatically named by oracle.

When one compare such a table in two schemas, Toad report difference between them regarding this is, looking from Oracle perspective not true. The problem is that Toad compare constraints by name and not constrains by type and columns (what is in fact most important). Similar thing happened with automatic pk constraint which generate unique index by oracle name.

Now imagine several hundreds of tables and a result one might get with comparison.

So mine proposal for next versions is to implement logic which will be more intelligent in compare such a cases.

Brg,

Damir Vadas

2

I just created this table in two schemas and ran the schema compare against it. On the “object types to compare” tab, I checked only tables and all types of
constraints. It reported no differences (as I expected, because I know I put some code in there to compare constraints regardless of name)

Could you provide more details about how you are seeing the differences?

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Tuesday, October 08, 2013 2:41 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion)

Compare schema improvement (suggestion)

Thread created by damir.vadas_531

Hi,

Toad 12.1 x64 (but affected all versions).

Here is example of constraint improvement that might help a lot…

Many Oracle users write create table statement like:

create table t (

COL1 NUMBER NOT NULL,

COL2 VARCHAR2(10 BYTE) NOT NULL

);

where constrained is automatically named by oracle.

When one compare such a table in two sachemas, Toad report difference them regarding this is, looking from Oracle perspective not true. The problem is that Toad compare constraint name and not
constrains by type and columns. Similar thing happened with automatic pk constraint which generate unique index by oracle name.

Now imagine several hundreds of tables and a result one might get with comparison.

So mine proposal for next versions is to implement logic which will be more intelligent in compare such a casses.

Brg,

Damir Vadas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

3

SQL> grant connect, resource to u1 identified by u1;

Grant succeeded.

SQL> grant connect, resource to u2 identified by u2;

Grant succeeded.

SQL> create table u1.t1 (
2 COL1 NUMBER NOT NULL,
3 COL2 VARCHAR2(10 BYTE) NOT NULL
4 );

Table created.

SQL> create table u2.t1 (
2 COL1 NUMBER constraint t1_col1_nn NOT NULL,
3 COL2 VARCHAR2(10 BYTE) constraint t1_col2_nn NOT NULL
4 );

Table created.

Now in toad:

differ.png
differ.png975×724 61.5 KB

Now I saw that resulting script sync is empty:

-- Play this script in U2@XE to make it look like U1@XE

-- Please review the script before using it to make sure it won't
-- cause any unacceptable data loss.

-- U2@XE Schema Extracted by User TOOLS
-- U1@XE Schema Extracted by User TOOLS

-- "Set define off" turns off substitution variables
Set define off;

-- No action taken. Source has system named constraint. Target has user named constraint.
-- No action taken. Source has system named constraint. Target has user named constraint.
-- No action taken. Source has system named constraint. Target has user named constraint.
-- No action taken. Source has system named constraint. Target has user named constraint.

but previous screen was confusing me somehow ...

Brg,

Damir

P.S.

Is there any option to put in sync script

SET VERIFY OFF;

as well automatically?

4

Oh, well that’s not what you showed me before. J

There is a difference in this case – one is named and the other is unnamed. You don’t want Toad to “un-name” the constraint in the sync script, do you?
I suppose I could add an option to completely ignore these types of differences.

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Tuesday, October 08, 2013 11:36 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion)

RE: Compare schema improvement (suggestion)

Reply by damir.vadas_531

SQL> grant connect, resource to u1 identified by u1;

Grant succeeded.

SQL> grant connect, resource to u2 identified by u2;

Grant succeeded.

SQL> create table u1.t1 (

2 COL1 NUMBER NOT NULL,

3 COL2 VARCHAR2(10 BYTE) NOT NULL

4 );

Table created.

SQL> create table u2.t1 (

2 COL1 NUMBER constraint t1_col1_nn NOT NULL,

3 COL2 VARCHAR2(10 BYTE) constraint t1_col2_nn NOT NULL

4 );

Table created.

Now in toad:

Now I saw that resulting script sync is empty:

– Play this script in U2@XE to make it look like U1@XE

–

– Please review the script before using it to make sure it won’t

– cause any unacceptable data loss.

–

– U2@XE Schema Extracted by User TOOLS

– U1@XE Schema Extracted by User TOOLS

5

John,

I said in mine first post:

The problem is that Toad compare constraints by name and not constrains by type and columns (what is in fact most important). Similar thing happened with automatic pk constraint which generate unique index by oracle name.

Glad that now is all clear.

:slight_smile:

But please could you add “SET VERIFY OFF” automatically (or at least as an option … in sync scripts???

Brg,

Damir

6

But it does compare constraints by type and columns. If neither constraint is named, Toad finds them and compares them and when the constraint is the same,
then no difference is reported. In the 2nd example, one is user-named and the other is system named, which is significant to some people. Are you asking for an option for these differences to be ignored?

About “Set verify off” – on the “misc options” tab, there is an option to include “set define off”. It’s a different option, but I think it will give the
same effect you are after.

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Tuesday, October 08, 2013 11:50 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion)

RE: Compare schema improvement (suggestion)

Reply by damir.vadas_531

John,

I said in mine first post:

The problem is that Toad compare constraints byname and not constrains by type and columns * (what is in fact most important). Similar
thing happened with automatic pk constraint which generate unique index by oracle name.*

Glad that now is all clear.

:slight_smile:

But please could you add “SET VERIFY OFF” automatically (or at least as an option … in sync scripts???

Brg,

Damir

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

7

Are you asking for an option for these differences to be ignored?

At least have an option for ignorance. And answer is yes I’d like to have difference as ignored.

It’s a different option, but I think it will give the same effect you are after.

Frankly it is not … especially if you join scripts in one bigger script and this bigger has some sql variables, like it is mine case.

:slight_smile: