In my company we always define synonyms for all the tables. I do not want to manually define them and actually I do not need to see them in my Browser. Is there a way to get the DDLs generated for those synonyms automatically, without defining anything?
Yes, use Table DDL Transformations.
Define the name for the Table DDL Transformation Script (in this example PrivateSynonym), define when it will be triggered (in this example After Create, meaning it will be the last DDL for the table), type the script:
Press Save. Then you can test the script by selecting one table from the Table to Test list and pressing Test.
This is what I got:
NOTE: the synonym is only created for the DDL not the design, you will not see any synonyms in the Browser.
Select the tables you want to generate the synonyms and press OK.
P.S. If the DDL File Editor shows blank you might have hit a known bug.
Workaround: Go back to Table DDL Transformations and select the script. Select After Create, type a space, remove it and save the script again.
You can use this script written by Marko Helskyaho, or write your own. This one creates a private synonym for all the tables selected for DDL generation.
/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script – as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
– model – relational model instance
– pModel – physical model instance
– table – the table in relational model
– tableProxy – table definition in physical model
*/
var ddl;
var lname;
var sname;
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
if(useSchema){
lname = tableProxy.getLongName();
sname = tableProxy.getName();
}else{
lname = tableProxy.getName();
sname = tableProxy.getName();
}
}else{
if(useSchema){
lname = table.getLongName();
sname = tableProxy.getName();
}else{
lname = table.getName();
sname = tableProxy.getName();
}
}
if(prompt){
ddl= “\nPROMPT Creating Synonym for ‘”+lname+”‘;\n”;
}else{
ddl = “\n”;
}
ddl = ddl +
“CREATE OR REPLACE SYNONYM Heli.”+sname +”\n”+
“FOR “+lname +”\n” +
“;”
ddlStatementsList.add(new java.lang.String(ddl));
