Multilingual Oracle Forms application

Yesterday I did a session for the developers of the project I'm currently working for.
It was about the introduction of a multilingual version of their application and the consequences of this version for their work.

The application is an Oracle Forms 10gR2 application with about 800 forms. About half of the forms are 100% generated from Oracle Designer.
The other half of the forms are hand-build in earlier versions of Oracle Forms (starting with Oracle Forms 4.5) and migrated several times.

The datamodel used for the multilingual enabling is relative straight forward. The first table is a table that contains all modules in the application.
A detail table from this table contains all user-interface items from a certain type that are in this module. For instance windows,text items, tabpages, checkboxes etc. etc. (all objects in a form). The last table is a detail table from the user-interface-items table that contains the actual translations of the items. Columns in this last table are for instance language and text_item_type (the user interface item [text item] has a prompt an a hint text).

In the pre-form trigger of all modules a call is made to a central program unit (the application is generated/build using headstart) where the contents of the tables are read for that specific module and the actual translation is started. All items are translated using the standard built-ins like set_item_property.

Main problem of this project is the way the metadata tables are populated. You could write logic that extracts this information from Oracle Designer. However, half of the forms is not generated so this approach does not work.

We decided to use a third-party tool. It is called the FormsApi Master (http://www.orcl-toolbox.com/fapimaster.asp).
This tool uses the Oracle Forms Open Application Interface to programmatically query, modify and create FMB/MMB/PLL/OLB modules. The tool uses a scripting language (similar to PL/SQL) that gives you full access & flexibility over the Oracle FormsAPI. We created a script that extracts all the information form the Oracle Forms modules and puts this information in a script. The scripts are used to populate the metadata tables.

Another problem that we were facing were the occurences of boilerplate text. Unfortunately there is no built-in like set_boilerplate_text. Therefore all boilerplate text has to be removed from the forms and replaced by alternatives. The older forms contained for instance items with boilerplate prompts (In forms 4.5 this was not a property from an item). So all occurences of this prompts have to be transfered tot the actual item. A boring and error prone job. Fortunately the FormsApi Master helps again. In the scripts corner of http://www.orcl-toolbox.com/fapimaster.asp I found a demo script that automates this job.

The information extracted from a fmb is not the only information that has to be translated. Almost every application uses referential data that is not maintainable by end-users. You can think of error messages, domain-information, module information etc.
I will post on this subject on a later moment