Apex Seminar

We, at iAdvise, are now working with Oracle Application Express for almost two years. We have a fixed-team of >8 people daily working within this environment and we realized already several projects.

We also want to share this knowledge to the outside world (e.g. by means of this blog). Therefore we organize, together with Oracle Belgium, our second Apex Seminar at the beginning of next month.

The seminar will be in Dutch. You can find the invitation here and, if you are interested, navigate to our registration page. Of course both the invitation and the registration page are developed in Apex, also the mailing we sent out to our customers and prospects.

What will be discussed during the seminar.

After a small introduction of what Apex is, we show how you can extend the standard functionality of the tool with reusable components which make the development process even faster and error prone.

The second part of the session is dedicated to some case studies and a user testimonial of the different Apex applications we have already built with our Apex team.

The user testimonial is presented by Roularta and deals with the project we realized for them (on time and within budget !). For their “Sportmagazine” we created as a replacement for a non-maintainable MS Access application, an editor for entering weekly all soccer-related data. Based on this data we generate (via SQL Word, see previous post) all kind of classifications that need to be published in their magazine.

We will also show some other cases to illustrate and highlight different features of the tool we already used.

The seminar is closed by a speaker of Oracle Belgium: he will give an explanation on Apex 3.0, the future roadmap and the Ms-Access migration facility.

XTra4o – Release 0.9.2 and Help

Last week we made our XLIFF-Translator for Oracle available to the public.

Based on the feedback we already received, we made some changes to the utility. See the About-tab in the application.

We also use this tool to try out some typical-issues-you-need in-every-application.

In XTra4o we implemented two different ways to provide Help to the user.

For every step we provide a visual-aid explaining where you are in the translation process.


For ‘experienced’ users it is possible to disable this help via the User Preferences.

A second way to provide Help is an on-demand approach with context-sensitive help. Clicking on the Help menu will open a dragable window, showing information of the current page.


This help text is fetched from a table which makes it possible to build a simple Apex screen on this table. Now a non-technical person can write the help text instead of a developer, which is always better …

Don’t hesitate to give it a try and provide us some feedback !

Apex: XTra4o

Yesterday we released our utility that helps in the translation process of an Apex Application. We gave it the name Xtra4o which stands for “XLIFF Translator for Oracle”.

XML Publisher (or BI Publisher) also uses XLIFF-files to translate their reports. One day, we hope to extend our tool so it will also be able to support the translations for XML Publisher Reports.

Within our company we already used this tool to translate different applications, but only for the languages English, Dutch and French. Therefore, we currently only provide a default dictionary for those languages.

If the tool will be used for other languages, we will be able, with your help, to extend our default dictionaries in the near future…

Apex: XLIFF Translator, generate the translated file.

The final step in our translation process is exporting the translated data back to an xml-file; i.e. the reverse process of step 1 where we translated an xml-structured file to a relational table-structure.

This time, the relational data has to be converted back to the original xml-structure (similar to the one of the imported XLIFF-file).

We need to recompose the following structure:

By using the standard SQL/XML functions available in Oracle XML DB, it is quite easy to realise this.

Don’t get frightened by the syntax. At first sight, it seems rather complex with all those nestings, brackets, …. But after a while, you’ll notice (that) it can be extremely powerful. Documentation on the syntax, can be found here.

We embedded the above query in a PL/SQL package which we use in the following Apex page:

So, now it is up to you. We made the tool available on the following URL. You first have to register (which only takes about 30 seconds) and then you can give it a try.

Any suggestions are welcome!

Apex: XLIFF Translator, the editor (step 3.2)

The difficulty with editing the XLIFF- file is that you do not know with which item/object the translation text corresponds in your application.

As explained in a previous blog, we discovered a way to link a translation item to a specific apex object type. This information is stored in the flows-table wwv_flow_translatable_cols$.

Therefore we decided to build a GUI-editor on top of that XLIFF-file.

In a first step, we loaded the XML-file as such in an XMLTYPE column, linked with a specific source and target language. By using SQL/XML features and X-Path expressions we extract the data and transform it to a relational structure.

Since all the data-to-be-translated is now seeded in our internal translation table, it is easy to build an user-friendly Apex page on it.

As you can see, we provide extra filtering options including the Apex object type. The screenshot shows only the labels of the tabs in our application.

There is also a small accounting on the work already done: we have already translated 3 of 4 tabs; for the complete XLIFF file, we translated already 175 of 400 items.

We extended the utility also with the notion of a ‘dictionary’. By default we have already preloaded a small dictionary with typical words that we see in every application: create, save, delete, search, home, admin, …

As user, you can extend this glossary with values (business terms) that are often used in your application.

In our example, we can decide to add the translation for “Photos”, “Foto’s” to our dictionary, by clicking on the right-most arrow:


Once added a check-mark appears between the two icons. A small help text on top of the page explains the different icons.

This mechanism can accelerate the translation work to a high degree: you can extend gradually your own dictionary and apply on a regular base those new words to the items that are not translated yet. We also provide a separate tab-page in the application where you can maintain the content of your dictionary.

In a next blog, we will explain how you can extract the relational data back to the initial XML-structure once the translation work is done.

Apex: XLIFF Translator, Step 3.1

As posted last week, we started developing our own utility to help us in step 3 of the translation process of an Oracle Application Express application.

The global idea is to create an Apex-frontend on top of an XLIFF-file.

XLIFF is an acronym and stands for “XML Localization Interchange File Format“. You can read all about is on following url.

We decided to take a pragmatic approach. We started with the XML-file as it is being exported by Apex into the XML translation file.

The following examples show some lines of the generated XML:

You notice that each translatable element has a source and target element, encapsulated in the tag, identified by an id-attribute.

So it should be quite easy to upload that file in an XMLTYPE-column and transform the xml-structure to a relational table by using one of the XML features of the Oracle Db.

Following SELECT gives a part of the query we used: t.xliff refers to the column storing the complete XLIFF-file in our custom table with the name apex_translation_files:

SELECT extractValue(value(xx), '/trans-unit/@id') id,
extractValue(value(xx), '/trans-unit/source') source,
extractValue(value(xx), '/trans-unit/target') target
FROM apex_translation_files t,
TABLE(XMLSequence(Extract(t.xliff, '/xliff/file/body/trans-unit'))) xx

In the XML-excerpt, you can also see that the same source-value can appear multiple times, but that there is a small difference in the value of the id-attribute.

It is clear that this id should have a specific meaning for the translation process, but we couldn’t find any documentation on this topic.

The id-value has 4-data fragments, separated by a ‘-‘: e.g. S-2-11453021424239212-144

The first element is always a “S”.
The third element contains typical an unique identifier of a record.
The fourth element is obviously the APP_ID of an apex application.

The second element intrigued me. It was a reduced set of possible values; that must be some typology of the text-to-be-translated.

We dived into the flows_020200 schema and noticed two important tables related to the translation process:

  • wwv_flow_translatable_text$: this table contains all data coming from step 2 in the translation process “Seed the Translable Text” and we discovered that the 3th element of our id-attribute corresponds with the translate_from_id -column

  • wwv_flow_translatable_cols$: this table contains all apex-elements that can be translated. The id-column of this table corresponds with the second element of the id-attribute in the xliff-file.

Putting all those elements together, we were able to built the first step in our utility: Upload and Seed the original XLIFF-file…

… and our previous XML excerpt presented in a relational way gives following result, where the value of the id-attribute “S-2-xyz” is referring to “the Navigation Bar Icon text” or “Icon Image Alt”


(will be continued …)

Apex: XLIFF Translator

We like the Apex solution to make multi-lingual applications; especially the idea of “one master-source, but multiple instances of the applications (one per language)”.

This approach reminds me of “Oracle Translation Builder”, the solution to translate the good-old Oracle Forms …

We explained already in another blog (entry of December 13th) how you have to deploy such a multilingual application. With the current version, there is one special point you should be aware of: when importing a translated application, use the same APPLICATION_ID. Hopefully Oracle will do something about this in future releases (see also this entry on the forum).

Following screenshot gives the main steps for translating an application, especially steps 1 till 4 are always necessary.

Don’t let you misguide by the hyperlink for step 3: the translation of the text is not (yet) part of Oracle Application Express. The real translation work from a source to target language happens outside apex.

You have to edit the XLIFF-translation file by yourself; either by using a simple text-editor, MS-Word or an XML Editor (XML Spy or JDeveloper).

We didn’t like those editors, because there is a lot of repetitive work.
Therefore we decided to build are own tool to help us in doing this work, a tool developed in Apex … of course !

What should be possible with this tool:

3.1. upload an xliff-file and analyze/seed the content to internal tables

3.2. make a translation for every source element

3.3. regenerate the xliff file with the entered translations.

The idea is also to provide a kind of dictionary ; so it will be possible to automate a part of the translation process for words that are repeatedly used.

A new tool is born: “The XLIFF Translator for Oracle”.
In the near future, we will put this application on-line.

Here you can see already a preview of the utility…

(will be continued …)