You can automate PowerDesigner by running vbScript directly or accessing the PD application objects from a COM aware language such as C# or VB. At this point I would like to keep everything simple and just run some vbScript from inside of the PowerDesigner Edit/Run Script console.

To really make some useful scripts in PowerDesigner you are going to want to leverage some shared code. This is easy in C# or VB but not so much in the basic vbScript environment. I can do most of what I want to do in vbScript but there is no obvious/simple way to include external files.

The PD documentation goes into a little detail about this topic in the Differences Between VBScript and OLE Automation section.

Here is a way to include a function and make it available in multiple script. Given a function called BrowseForFile that returns a string...

lib\BrowseForFile.vbs :

function BrowseForFile
... blah blah blah ...
end function

You can reference it at the top of a calling program and then call the function all that you like...

ExampleInclude.vbs :

executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile("lib\BrowseForFile.vbs").readAll()

output BrowseForFile()

So just substitute your filename in the openTextFile call. You can call this multiple times to include more than 1 file. As best I can tell the path is relative from the current directory. I will be making some useful functions and more examples available in the near future.

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:

And drop Sequences:

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.