Running Oracle Designer Generation from Ant and Hudson

Introduction

Oracle Designer is a windows client-server development tool that is meant to be manually operated by a developer. Anyone trying to integrate Designer with an automatic build environment will find that it does not provide an API or a commandline version to kick-off any generation automatically.

There is however a hook that can be exploited by generating so-called GBU files directly from the Designer Repository. These GBU files are then fed to an executable called dwzrun61.exe that executes the actual generation of DDL scripts and forms. 

This article describes how this can be done using examples from a real world situation. It shows how to generate the GBU files, the different strategies that can be followed and some of the pitfalls you might run into trying to pull this off yourself.

The code of the program we wrote can be found on here and is free to be adjusted to fit any other situation than ours.

What are GBU files?

The Designer module for generation, Design Editor (DE), has a function called Batch Generate that offers non-blocking generation of objects in batch mode. DE accomplishes this by writing all information that is captured in the Generation Wizard to a control file, and passing this implicitly to a separate executable (dwzrun61.exe) as an input file.

These control files are called GBU files (after the file extension .gbu that DE gives them). They can be explicitly named and saved on the final dialog of the Generation Wizard by selecting the option to save the Generation Details.

After having saved this file, it can be opened from windows file explorer by using ‘Open With…’ and selecting the executable dwzrun61.exe from Oracle Designer bin directory, or from the command line:

 dwzrun61.exe myfirst-gbu-file.gbu

This will result in the opening of the generation window and the start of the actual generation.

What is inside these GBU files?

The GBU file is a binary serial data file and can be examined (and reverse engineered) by an editor that has a Hex mode. It’s content basically reflects all the settings that are made in the Generation Wizard of Design Editor:

• connection details for the Designer Repository and the WorkArea to connect to

• the type of objects to generate. The types are Clients Applications, Server Model (ddl scripts), Table API, and some others. A GBU file can only contain objects of one single type.

• the several generation options, these differ per type

• whether to generate against a target database, or not. And if so, the connect data for the target database.

• repository id’s (IRID’s) and names for the actual repository objects to generate

• the name of logfile and the name and location of the output files that are to be generated.

In principle it is possible to completely reverse engineer the structure of the GBU files by examining the content, making an adjustment to the settings, recreate it and look for the differences.

In our project is was sufficient to create GBU files of type Server Model (ddl), Table API and Client Applications (forms).

How to generate the GBU files?

I wrote a Java commandline application, GBU-generator, that generates the GBU files that were needed in the client’s environment. The code is very loosely based on a previous code written by Rob Swinkels from Oracle Netherlands in 2005.

The code can be found on Github and can be changed to fit anyone’s purpose. It uses Maven to build and the end result is a so-called shaded jar, an executable jar file that contains the code including all underlying libraries so there is no need for any external classpath settings.

The GBU-Generator performs the following tasks:

1. Queries the Designer Repository to find names and IRID’s of the designer objects that belong to a single configuration in a certain WorkArea. That is, a bundle of objects that need to be generated for a specific change or bugfix.
2. Queries the Designer Repository to find the parent PL/SQL packages that a procedure or function resides in.
3. Queries the Designer Repository to find the Forms that use a specific Reusable Component
4. Writes a Drop Triggers script (in order to have smooth DDL generation run later)
5. Writes GBU files for every single object in the configuration.

Specific settings like repository database connection details, the target database connection details and other settings need to be provided by command line options, or through by a property file.

What does the overall process look like?

The overall process is scripted using Ant and configured as an Hudson job. On our project, it takes the name of a Designer Configuration as a Hudson input parameter. It then executes the following steps:

1. GBU file creation
   GBU-Generator queries the Designer Repository for the objects that belong to this configuration and creates a GBU file for every object.
2. Execution of dwzrun61.exe
   For every GBU file the dwzrun61.exe is run. The result is a collection of DDL files for Tables/Sequences/Views/Packages/Indexes and a set of Forms.
3. Deployment of the generated objects
   Final task consists of running the SQL scripts against all the databases on a specific environment. The forms are compiled and copied to the designated environment.

How to query the Designer Repository?

To include a Designer object into a GBU file and make it eligable for generation you need it’s type, name, and it’s Repository id (IRID). The GBU-Generator uses this query to acquire these for a single configuration: 

select * from ( 
  select nvl(plm.plsql_module_type, tpe.name) type 
  ,      member_obj.name name 
  ,      cmb.object_irid irid 
  ,      member_obj.sequence_in_branch obj_seq 
  ,      member_obj.vlabel obj_vlabel 
  ,      config_obj.sequence_in_branch config_seq 
  ,      config_obj.vlabel config_vlabel 
  ,      row_number() over ( partition by tpe.name 
                             ,            member_obj.name 
                             ,            cfg.name 
                             order by member_obj.sequence_in_branch desc ) rn 
  from   sdd_configuration_members cmb 
  ,      sdd_configurations cfg 
  ,      sdd_object_versions config_obj 
  ,      sdd_object_versions member_obj 
  ,      rm_element_types tpe 
  ,      ci_plsql_modules plm 
  where cfg.ivid = cmb.config_ivid 
  and   cfg.ivid = config_obj.ivid 
  and   cfg.name = :configuration
  and   cmb.object_ivid = member_obj.ivid 
  and   tpe.id = member_obj.logical_type_id 
  and   plm.irid (+) = cmb.object_irid) 
where rn = 1 
order by type
,        name

Next to type, IRID and name we also query the object’s version label and the configuration’s version for debugging purposes. Adjust this query in the Java code to meet your situation’s specific needs.

Useful tables in the Designer Repository to get you to your desired set of objects are:

SDD_WORKAREAS //all workareas
SDD_WORKAREA_SPEC_ENTRIES //inclusion of configurations in a workarea
SDD_CONFIGURATIONS //all configurations
SDD_CONFIGURATION_MEMBERS //objects included in a configuration
SDD_OBJECT_VERSIONS //all versions of an object
RM_ELEMENT_TYPES //types for all objects

When writing queries against the Designer Repository, be aware that all object definitions in the repository are uniquely identified by a repository id (IRID). Furthermore, almost all objects are versioned through a version id (IVID). Note that Configurations are versioned as well.

One DDL script per object or not?

A choice needs to be made in how you want to bundle designer objects into the GBU files. Either you generate a GBU file that contains all objects of a certain type that need to be generated, or you generate a GBU file per object.

The first option is the strategy taken by Design Editor itself.  A dwzrun of such GBU file generates the least amount of create scripts and includes aggregate master sql scripts for all database objects.

We choose the second option and this is how GBU-generator on Github currently works. It creates single DDL scripts per database objects, including the object’s name in the filename.  This results in a larger amount of scripts but allows for more fine grained control and validation. It is more useful in situations where occasional post-generation adjustments need to be made and the scripts are part of a release that contains non-designer generated stuff as well.

For Forms generation there is no difference between these two strategies.

How to configure the Hudson server

For this all to work on Hudson (or Jenkins) it is necessary to run Hudson on a server that also has a working Oracle Designer installation with a valid SQL*Net configuration. This most probably means that it has to be a Windows server.

Apart from that, the creation of the Hudson task is just like any other Hudson task that is configured as an Ant script.

It is recommended to dump the output of the dwzrun61.exe to the Hudson console since in this setup the users never get to see the dwzrun6 output window. This can be done using the following Ant script:

<echo message="dumping logfile..."/>
<loadfile property="mytext" srcFile="${gbu-file-dir}/dwzrun.log"/>
<echo>${mytext}</echo>

Also make sure to kill the dwzrun61.exe process after the generation has completed (or in case of a timeout) in order to prevent stale windows and processes at the Hudson server.

<echo message="Kill the dwzrun61.exe process ..."/>
<exec executable="taskkill">
  <arg line="/F /IM dwzrun61.exe"/>
</exec>

Some problems and how we overcame these.

Trying to get the generation process completely automated with Ant we ran into several problems. Here we mention the main ones and how we solved or worked around them.

Server Model generation starts asking questions with nobody to answer

When the dwzrun61 run starts for a GBU file of type Server Model (tables/views/packages etc) it might encounter objects (indexes, triggers, constraints and fk’s) in the target database that are not defined in the Repository. It then opens a popup asking the user to leave these be or drop them. This is all fine as long as there is a user to answer these questions but it is a blocker when the process runs unattended on a Hudson server.
In our situation, this happened always for triggers since the Table API was not retrofitted into Designer. We worked around this by dropping all triggers for a certain table before running the Server Model gbu and making sure to always run the Table API gbu after the Server Model so the triggers would be regenerated. We had the GBU-generator specifically generate a drop-triggers script for this purpose.
Furthermore, we had the Ant script always spawn the dwzrun61.exe in a separate thread and put a timeout on it, in order to never block the entire generation process in above mentioned situations.

How to detect a finished generation?

The dwzrun61.exe writes it’s output to a logfile and opens a window in which it gives feedback on the object generation process. Unfortunately, this window does not close automatically when the process is finished. The Ant task <resourcecontains> allowed us to monitor these logfile and check for the text ‘Batch Process Completed’ to appear.

<echo message="Polling logfile contents for text 'Batch Process Completed' ..."/>
<waitfor maxwait="30" maxwaitunit="minute" timeoutproperty="timeout">
  <resourcecontains resource="${gbu-file-dir}/dwzrun.log" substring="Batch Process Completed" />
</waitfor>

Impossible to generate DDL against a database wihout executing it.

When generating DDL for a single object by hand in Design Editor,  alter-scripts can be generated against a target database without actually executing this DDL. This is desirable in an any environment where you want to roll out to multiple databases or when the actual running of scripts is assigned to different persons like a DBA’s.

Unfortunately, we could not find this switch in the Batch Generate function of DE. We found a rather crude workaround for this problem by temporarily disallowing DDL execution on the target database by using a schema trigger.

create or replace trigger no_ddl
  before ddl on schema
begin
 raise_application_error( -20001, 'DDL execution temporarily not allowed;');
end no_ddl;
/

Thus, the DDL is not executed (resulting in a lot of erros messages in the dwzrun.log) but the related script gets generated. Make sure to disable this trigger afterwards.

Eventually we did not use this workaround but ran the generation against a reference target database that was allowed to be altered. We only specified this target database for tables and sequences since these are objects that should be ALTERed in an production database. All other objects result in DDL that uses CREATE OR REPLACE irrelevant of the situation in a target database.

Not all options for Forms generations are derived from GBU file

It turned out that for the generation of Forms (Client Applications) not all controls are set in the GBU file. To get everything running smoothly and have all generated forms end-up in the designated directories, we had the Ant script insert some registry keys before the forms generation kicks off. These include settings for the template and object library path.

Conclusion

As we have shown, generating GBU-files by a custom program opens a possibility to integrate Oracle Designer with an automated build server like Hudson. Getting it to work is more than a piece of cake and requires some time, perseverance and decent knowledge of Designer, Ant and Java though it can most certainly be pulled off with any other build automation tool and modern programming language.

When Designer generation is still a major component in your development stack it is certainly worth the effort to look into this. Even without a build server like Hudson in place, having a script run the entire generation process directly from the repository can be a real timesaver for developer as it can make obsolete the tedious and error prone work of manual generation using Design Editor.

Feel free to contact me for more information.