Toad World® Forums

Extract DDL Constraint and synonyms


#1

Hi

When extracting DDL of a synonym the target gets no schema indication.
That is a very old problem/feature. I think it should have schema indication IF the schema is other than the synonym schema.

That said, in extract DDL of a Constraint we get the schema of the table even when the schema is the same as the constraint.

Maybe for a 6.4 version?

Thanks
Regards


#2

Hi Filipe,

There is an option in Preference, Extract DDL -> General: Prefix with Schema name. This option is OFF by default. Have you tried this?

Thanks,
Vincent


#3

Hi

With that option ON I get the schema in all the points…

But as I report (with the option off) in constraints we get the schema of the table (I think its a bug)

I think that that preference will not be very useful for synonyms due that:

  • if we have the same users we want to use option ON
  • if we have distinct users we still want to use option ON so we can make a find/replace

I think that a preference to do as I suggested: on being on the same user no schema name attached, distinct schema attach schema name

Regards
filipe


#4

Hi Filipe,

First, you found a bug. Thanks for the work :). This option is broken for constraint in 6.2. I will log this.

I understand the problem of when to have the schema and when not to. I’m thinking about what you suggested. Can we call it like “Only for distinct schema” and as a sub option of “Prefix with Schema name”? In this way, things will work like:

  1. Prefix with schema name - ON
    1). Only for distinct schema - OFF
    a. the object to be created - add schema
    b. the objects being referenced - add schema
    2). Only for distinct schema - ON
    c. the object to be created - no schema
    d. the objects being referenced
    d1. objects having the same schema like the object to be created has - no schema
    d2. objects having different schemas from the owner of the object to be created - add schema

  2. Prefix with schema name - OFF
    never add schema for any object

e.g.,
CREATE SYNONYM user2.object2 FOR user1.object1;
here, object2 is the object to be created; user2 is the owner of the object to be created; object1 is an object being referenced; user1 is the schema of the object being referenced. So when the new preference “Only for distinct schema” is ON, the DDL will be:
CREATE SYNONYM object2 FOR user1.object1;

I don’t think we need to take care of what current logon user is. Because we don’t know where the DDL will be used.

This is pretty all I think of for now. Would this meet what you suggested? Please let me know if you have any other idea.

Thanks,
Vincent


#5

Hi Vincent

Yes , It looks great!

It will be in 6.3 ;-)?

Thanks
Filipe


#6

Hi Filipe, I have created enhancement CR for this. but I’m not sure if we can include this in 6.3. I will keep you posted :).