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

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 …

Apex: Housekeeping Your Flow Files

In my previous blog, I explained the problem we encountered with flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES.

I did some further investigations on the content of that table to 1) detect the ‘big size spenders’ and to 2) see whether or not there exist some orphan entries. I also wanted to know if we could write a delete statement to remove those obsolete records.

My research was not that easy …and not really satisfying…



This table is really used for all kinds of uploads of files into an Apex environment.

Apex uses different types of repositories:

  • an export repository
  • an image repository
  • a css repository
  • a text data load repository (accessible via Utilities)
  • workspace import/export repository (via Internal)
  • SQL Workshop script repository

The main columns I concentrated on are:

By executing following query (as user FLOWS_020200), we can see which (uploaded) files exist and how much space they are taking.

SELECT o.security_group_id,
o.flow_id,
o.filename,
o.doc_size,
o.mime_type,
o.file_type,
o.content_type
FROM wwv_flow_file_objects$ o
ORDER BY 1,2

By sorting on DOC_SIZE DESC, we could easily isolate those files that took a lot of space in the database. Based on the type, the developer was asked to clean up his Apex repositories.

For a lot of entries, we could not that easy map to an existing repository.
We noticed very often that the value of FLOW_ID equals to 0.

At first we thought that we could remove all those records. When we uploaded a file via our custom application and did not delete it from the APEX_APPLICATION_FILES view, the entry remained in the files-table with flow_id = 0.

On the other hand when browsing the data in the table, we discovered based on the filename and mime type that workspace images and uploaded css-files are also stored that way.

And in some cases, not only the flow_id = 0, but also the security_group_id = 0.

Based on the filename and the content we could for almost 100% certainty say that all those entries were obsolete and could be deleted. We used the following statement:

DELETE FROM wwv_flow_file_objects$
WHERE security_group_id = 0
AND flow_id = 0
AND file_type IS NULL

The cleaning of other entries we did on a record-by-record base.

Lessons learned

  • when uploading a document/an image into your custom application ALWAYS delete the files in the apex_application_files when you have transferred it to your own table

  • check on a regular base your oracle alert file
  • clear the different repositories within Apex when you do not longer need that file !
  • when deleting manually records (check, double check and take a backup)

Apex: flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES

End of last year, we had a strange problem in our test environment while testing an internal application for maintaining our library of IT-related books.

When adding a new book title, we got from time to time a “page not found error” with a reference to wwv_flow.accept in the URL. Strangely enough, sometimes it worked, sometimes it didn’t ! And this same page always worked in the development environment.

At first we thought it was a time-out problem of the http-server. We checked the Apache log files, but didn’t find any trace of an error.

The day after another colleague had the same problem when he tried to import an apex-application in another workspace.

So, the problem wasn’t application specific anymore, it happened also when doing standard apex-functionality.

We gradually understood that it had something to do with file-handling.

When processing the page that creates a book-entry in our library application, one of the steps is the upload of an image with the book-cover. We detected that when uploading a small image (e.g. an icon) the page was treated as it should, with big files it caused a problem.

So, we decided to run an audit of the database. Therefore we used Plato a free, handy tool for auditing and tuning an oracle database, developed by one of our Cronos colleagues — btw. you can donwnload the utility from dba-village –.

After running the script, we noticed the following warning in red !


So the problem was that the tablespace in which the schema FLOWS_FILES was created could not allocate extra space because the Maximum Size for auto extension (set to 200 Mb) was almost reached.

If we would have verified at day-1 the standard Oracle Alert file located on the database server, the problem would be clear immediately. Afterwards we noticed that for every attempt that caused load-error, there is an entry in that log file saying: “ORA-1691: unable to extend lobsegment flows_files.SYS_LOB0000053172C00017$$ in tablespace HTMLDB_FILES.”

To resolve the problem we could have decided to extent the tablespace, and problem would be fixed; but before doing that we wondered why that tablespace had reached those limits.

FLOWS_FILES is one of the 3 standard schema’s that are created when installing Apex. This schema contains only 1 table: WWV_FLOW_FILE_OBJECTS$. On top of this table are the views/synonyms HTMLDB_APPLICATION_FILES and APEX_APPLICATION_FILES created in THE FLOWS_020200 schema. This table contains ALL files that are uploaded in Apex, be it via a custom application, or be it via the Apex Development Environment.

We investigated the WWV_FLOW_FILE_OBJECTS$ table and we noticed that the table contained many and many entries that shouldn’t be there anymore.

The main reason was that some of the developers forgot to delete the (intermediate) files from APEX_APPLICATION_FILES once it was copied into their target table. So, we isolated the obsolete entries and deleted those records directly via SQL.

Another reason was that some people didn’t have the habitude to clean the Export Repository from time to time.

One of the coming days I will blog on the query we used to examine that table and on our findings related to the content of the table.