Name Substitution can be used to change the name of an object (user, role, table, index, or tablespace) while generating the DDLs. These changes do NOT affect your design. The most common use case is that in test environment something is different from the production environment but not so much that it would be worth maintaining two separate physical models.

Let’s assume that everything is the same in both test and production except the schema owner of the objects: in production they are owned my PROD and in test by TEST. To be able to get the DDLs for creating tables for the test environment we double-click the design name (or right-click and select Properties) in Browser, then go to DDL | Migration.

NameSub1

Press the green plus and define the rule for Name Substitution and press Apply.

NameSub2

Now the rule for changing word “PROD” to “TEST” has been defined but this will not cause any action while generating the DDLs unless you specifically tell the tool to use this.

Let’s generate the DDL (File | Export | DDL File). Now go to Name Substitution tab and make sure the rule has been selected (Selected is enabled):

NameSub3

You can also create new rules in this screen. Just press the green plus and add the rule.

Now go to Object Types tab and select the object types you want this rule to be affecting:

NameSub6

Now you have defined the rule and which object it will be affecting but still if you now press OK (to generate the DDLs nothing will be changed). Next thing to do is to enable the Apply Name Substitution checkbox on the bottom of the screen:

NameSub7

Now press OK.

Instead of getting a DDL like this:

CREATE TABLE PROD.Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
LOGGING ;
ALTER TABLE PROD.Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

you get a DDL like this:

CREATE TABLE TEST.Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
LOGGING ;
ALTER TABLE TEST.Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

 

 

I know, I know. You can have the DDLs without the schema owner, connect in the database with the right credentials and problem solved without the schema owner added to the script. But I do make mistakes, like connect to a wrong database, so I prefer having the DDLs as human error free as possible. Besides this is just a simple example how Name Substitution can be used 🙂

Advertisements