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 …)

Apex: Report Sort Option showing strange behaviour …

A few days ago I was making a basic report page in ApEx, but when I tried to run the page I got the next error:

Although the report was based on a simple select-from-one-table, it took me quite some time to found out what went wrong:

The first thing I did was checking my SQL Query:

My query is running smoothly in my SQL Developer and I didn’t modify anything on the column/report attributes; so I expected my report to display normally.

So why is it producing this “ORA-00911: invalid character” error?

On the Oracle apEx Forum I made a post explaining my problem.

Meanwhile I did some further investigations; I removed my custom header-names and my sort option. I was amazed when all of a sudden my report was displaying normally like nothing ever happened. When enabling the sort function again , the above error showed once more.

When posting this new info on the forum, someone made it clear to me that I had to remove the ‘;’ at the end of my query. I followed this advice and enabled the sort option again; and indeed my report was displaying normally with the sort option on. The explanation is that when you establish a sort via the wizard, Apex is dynamically extending your SQL statement to apply the order by clause and the “;” makes the SQL invalid.

So to cut a long story short: don’t use a ‘;’ at the end of your SQL query in your report source, the query runs fine without it and you have no problems enabling other options.

Using dialog-windows in an ADF Application:

I encountered a very weard problem when testing my adf application using resourcebundles to render the labels and messages in the proper language. All labels/messages were translated properly in my application except for my dialog-page, a popup-window used for List-Of-Values.

After a lot of cursing, re-trying I’ve finally found the issue, which isn’t exactly the behaviour you would expect! You have to put the f:loadBundle tag inside the adf/html tag for the dialog-screen for the main screens this isn’t necessary, but to have internationalization in your dialog-screen you need to put them in that exact order!

The code samples below show you the difference between the dialog-screen code and the main-screen code:

Main-window(code sample):
<f:loadBundle basename=”EPremierUI” var=”uires”/>
<f:view locale=”#{userInfo.locale}”>
<afh:head title=”#{uires[‘eDossiers.title.header’]}”>

Dialog-window (set f:loadBundle tag inside the adf:html tag):
<f:view locale=”#{userInfo.locale}”>
<f:loadBundle basename=”EPremierUI” var=”uires”/>
<afh:head title=”#{uires[‘lovIndieners.header’]}”>

What you have to remember here: In a dialog-screen in ADF you have to set the f:loadBundle tag inside the adf:html tag

Deploying a User Interface Application to the Oracle Bpel Server:

When you want to access a deployed business proces via a User interface, e.g. a JSP-page, you will use the Oracle Bpel Java API. This Api gives you the possibility to instantiate deployed processes of your Oracle Bpel Server and to get process audit information, etc.
There are several examples available of how to build such a User Interface (Tutorial 102.InvokingProcesses, technet, …) but information about deployment isn’t available.
If you take a look at technet you will find a lot of questions about best practices, errors when instantiating this UI, … so I thought it would be very nice to have a best practice about the deployment-issues of a User Interface application on the Oracle Bpel Server.

Best practice on deployment of a UI-application on the Oracle Bpel Server:

  • Create a new deployment profile, WAR-file
  • In the general-topic (the default shown) choose the second radio-button ‘Specify J2EE Web Context Root’ for the Web Application Context Root. Enter ‘AsyncLoanFlowUI’ for the contextroot.
  • Add following parameter to the platform ‘Standalone OC4J’-node -parent orabpel (otherwise you will need to restart the server to add this parameter manually everytime you redeploy the application to the Oracle Bpel Server, there’s a post about this on Clemens-blog)

  • Right-click the deployment profile and choose to create a new connection, the Application Server Connection Wizard will be shown
    o Connection name: LocalBpelServer
    o Connection Type: Standalone OC4J

You don’t need to have a build.xml-file or application.xml-file, you only need to have a deployment-descriptor in which you’ve added the parent-parameter to be able to access the UI in your Oracle Bpel Server.

Apex, RTF and MS-Word

At the end of last year we had to re-write a MS-Access application with Apex for maitaining information on the Belgian football competition. Besides storing all kind of information related to the teams, players and games, this application needed also to provide a module for exporting the entered data to different RTF output-files.

Our first idea was to write a PL/SQL package in analogy with the well-known HTP-package, providing for every special formatting (like bold, italic, new line, …) a specific function, wrapping the rtf-tags around a given text. But when we looked at the RTF specifications, we had to recognise that we could not accomplish this on time and on budget.

In the meanwhile we googled for alternative solutions, but couldn’t find any that could be easily integrated in Apex and PL/SQL.

In the end we realized that we already had a tool at ours disposal that is well suited to solve this problem: namely SQLWord, a product developed by Sequel Solutions.

This product gives you the possibility to retrieve Oracle data into Microsoft Word documents. It already exists for years; initially as a client-server tool where it could be integrated with Oracle Forms.
When Oracle Forms moved to the web, the tool was extended with a possibility to create a document from within a browser (based on Mod Pl/Sql).

In fact, those MS-Word documents are RTF-files … and that was what we were looking for !

Additionally, SQL Word provides also a PL/SQL interface that makes it possible to run SQL Word in a kind of batch-mode. By use of DBMS_JOB and an Oracle Directory we could generate all desired RTF-outputs in the background.

So, SQL Word was the ideal solution for the given problem and well suited to integrate with Application Express. When you need to create MS-Word documents, this product can be considered as an alternative for XML Publisher.

SQL Developer and BLOBs

SQL Developer is a very complementary tool to Apex.
Although, you can do all basic SQL stuff via the SQL Workshop in Application Express, sometimes you need a more powerful tool like SQL Developer, especially when you really need to ‘develop’ in SQL and PL/SQL.

One of the SQL features I like about SQL Developer is the possibility to look at the content of a BLOB column.

This feature was very useful during my quest on the content of the flows-files table. And I don’t think other tools like TOAD, SQL Navigator, PL/SQL Developer has those possibilities …

Just by clicking on the blob-column in the result grid, a popup window is opened where you can specify that you want to see the content as text or image.

When this column contains plain text, there is no problem at all to have a look at the content. The following example opens a tab-delimited file.

SQL Developer can even visualize binary images (gif, jpeg, bmp). Very handy …