PowerDesigner Generate Script Settings for Oracle
PowerDesigner generally does a pretty good job of creating DDL to generate a full database or to generate a delta script to upgrade one structure to another. I am using 12.5 right now and have noticed there are some cases which produce scripts that don't run without errors. Sometimes PD likes to make sure tmp_ tables don't exist so it drops them whether it knows they exist or not. Also sometimes it seems to freak out when dealing with sequences and tries to recreate them all even when just 1 has changed. I am in the process of an upgrade to PD 16 and will see how they address these issues, but wanted to get these techniques recorded for posterity.
This script generation is defined by the DBMS Definition. This is how PD supports syntax of the multiple databases it targets. If you open the generation you can find all kinds of information about the behavior of the scripts PD creates. Keep in mind you may want to copy your own version of the database definition or you risk changing it for all models that you work with. Also if you have colleagues that want to leverage your script changes you may need to give them the DBMS definition file or you might consider the option to embed the DBMS Properties into your model. It is really just what works out for you with your workflow.
Block Terminator
First things first. To get the most use out of your scripts when generating from Oracle I suggest changing the delimiter setting. Navigate to Database->Edit DBMS Settings. The default delimiter is a semicolon (statement terminator). This is fine for the default generation options but we will be incorporating some Anonymous PL/SQL blocks to catch errors, and those must be submitted as the slash (which signifies buffer submit). This buffer submit is also used in the scripts I generate for use with migration tools such as myBatis Migration Tool. Open up the DBMS Properties screen an navigate to Script->SQL->Syntax. Change "Use end of block character by default" from No to Yes.
Allowing Scripts to run with Less Errors
Now we can look into some specific statements that error out often. I have noticed PD likes to drop tmp_ tables, indexes, and sequences often during the Modify Database scripts. Often these objects do not exist and this causes an error when generating scripts. I like that PD is being careful to avoid catastrophic errors down the line, but I am strict about my scripts (WHENEVER SQLERROR EXIT FAILURE) and this puts the brakes on my deployments all too often.
We can see the definition of the DROP TABLE command here and change it to something more scripting-friendly. The original statement was:
drop table [%QUALIFIER%]%TABLE% cascade constraints
And here we changed it to an anonymous PL/SQL block that will raise an error when we get anything other than exactly what we wanted to trap, ORA-00942 TABLE NOT FOUND.
In a similar fashion we can handle Drop Indexes:
With great power...
...comes great responsibility. Beware that you are changing some fundamental behavior of PD script generation. I am starting out just trying to smooth over some PD script generation annoyances, not trying to change any fundamentals of how PD produces scripts. But the whole DBMS Definition is in front of you so at least you are able to find out _why_ PD may be doing something unexpected in its scripts.