Apex and Charts

In Application Express 2.2 and previous releases is it possible to build a Chart with the use of the SVG Viewer technology of Adobe. A time ago Adobe has decided to discontinue support for Adobe SVG Viewer starting from January 1, 2008. They even plan to remove Adobe SVG Viewer from the Adobe.com download area on January 1, 2009, and redistribution by third parties is not allowed by the license.

Consequently the end of building a SVG Chart in Apex is near. The Apex development team came out with another solution for building charts, the Flash Chart.

Flash is known for his rich animations. With the use of Flash, you will be able to have full control in the design of your chart. The developer’s creativity can be maximized. Despite the more possibilities, it will not be more difficult to create a chart in an application. The Flash Charts in Apex are set the same way SVG Charts were set. The chart definition will also consist of several query series and its attributes can easily be set in the chart attributes. When you want a more advanced chart with rich animation, you can update the source of the chart in its region.

If you can not wait until Apex 3.0, you can use the XML Charts developed by Denes Kubicek. These charts also use the Flash technology. The difference with Apex 3.0 Flash Charts is that the charts are setup with the use of database objects. Denes created a way to define charts more generically. The chart’s definition will be setup from the database. The chart settings are kept in a database table as well as the chart layout is hold as several templates in another table. With a request from the application to a procedure in the database package, the chart definition will be set and sent back to the application.

The XML charts of Denes Kubicek, have certainly their advantages. It isn’t necessary to define separate series. All the chart values will be sent by one SQL query. It is also possible to debug the chart definition. The XML that is used to setup the chart could be loaded in a predefined Oracle directory.

Because the XML charts have most of the advantages of Apex 3.0 Flash charts and because it is much more generic I recommend you to go for this method when you have to create several charts in a specific layout. It will also be easier to define one template layout that is used for more than one chart.
When you only have to create one simple chart, I recommend to not choosing for an XML chart. An Apex 3.0 Flash chart will be better in this case because it is easily built in the Apex tool and the database schema doesn’t have to be extended with three objects just for creating one chart.

For more information about XML Charts, have a look at Denes’ sample application:
http://htmldb.oracle.com/pls/otn/f?p=31517:74

The charts represented in some examples:

SVG Chart

Flash Chart

XML Chart

Reporting in Apex 3.0

In the next Application Express release, PDF reporting will be included. No more external tools are needed because the reporting will be built in the Apex tool.

There are several formats to report from the Apex tool. PDF, HTML, Excel as well as Word will be supported. The reporting can be setup from two different places.

It is possible to print out the report region result with the use of a link under the report. The second place to implement a pint-out can be done with the use of a button, list item or other navigation item. The item calls to a general defined report definition.

For defining a print-out link under the report, Apex introduces a third, new navigation tab. This tab is positioned next to the Report Attributes tab in the Report’s tab-set. The tab is named Print Attributes. Here you can define the label of the link and other attributes like font colors and alignments. By default all columns will be printed, but they can be limited to a specific output.


The implementation of a general defined report is set in the Shared Components of the application, more specifically in the Report Queries of the application. A Report Query is the definition of a report. The report data will be retrieved from a SQL query result. The query can consist of bind variables, these are items that are known in the session, for example application items or page items.


The report layout is based on a predefined Report Layout. It is possible to choose for the default general layout or you can choose to create your own layout.

When you choose to create you own layout, you need to use the Oracle XML Publisher Template Builder for MS Word. With this tool, you can make a template that can be used to show the report output.

As the name speaks for itself, XML Publisher makes use of XML files. So we need a XML file before we can start defining our custom layout. Apex gives the possibility to download a print of the query result in a XML file. With the data of the XML file, you can create a RTF file in the XML Publisher Desktop tool.


The layout(s) of the report is separated from the definition of the report. The RTF files or XSL-FO files can be uploaded in the Report Layouts of the application. This can also be found below the Shared Components of your application. After the report template is uploaded the report query definition need to refer to your custom layout.

Finally when the report query definition is complete, you need to implement a call to the definition on one of your pages. Add the end of the report query wizard, an URL is put together.

By example:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=MyReport

What’s remarkable about this URL is that the request is send to page zero and a new parameter is known in the URL structure. The parameter’s name is PRINT_REPORT and it‘s value in this case is MyReport which refers to the name of my report query.

That URL will be used to integrate the report into your page(s). You will use the URL as a target for buttons, list items or other navigation items.

What happens when the navigation item is pressed?

  1. The parameter PRINT_REPORT refers to the defined report query MyReport.
  2. The XML document will be generated with the returned data from the SQL Query.
  3. The XML file will be used by XML Publisher to load the data in the chosen report layout.
  4. The layout will be formatted in the right report format.
  5. A download window disappears at the client to open or save the file.

Have a look at some examples:
http://apex.oracle.com/pls/apex30/f?p=2381

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 !

XML Publisher won’t start up anymore … !!!???

A month or two ago, I installed Oracle XML Publisher on my notebook. In order to run XMLP you need to run the “Oracle XML Publisher Enterprise start” (OC4J) via the Start Menu first. Next, you can run XMLP in your internet browser.

First it worked fine, but one day, XML Publisher wouldn’t start up anymore… and apparently the cause was the OC4J that failed to get up and running, as starting the OC4J start-up script resulted in the following error:

2006-12-04 09:47:57.453 ERROR Failed to set the internal configuration of the OC 4J JMS Server with: XMLJMSServerConfig[file:/C:/oracle/product/10.1.0/db_1/oc4j/ j2ee/home/config/jms.xml]

As my knowledge of Java is very limited, and there was nothing else I could think of that would solve my problem, I decided to simply re-install XMLP and … it worked again. But after a while, the old problem came back … Only did re-installing the software not work this time …

I went to look in the OC4J log files, and found that apparently my IP-address had shifted. Via the command ipconfig, I figured out that the IP-address causing the OC4J not to start, corresponded to my Wireless Network Connection… I disabled the WNC, and … EUREKA! It worked fine again. At least: I was able to start XMLP again, and fortunately I had my LAN-cable plugged in, so that I could access the database and continue with what I was doing.

But still, I kept wondering what went wrong, and why it went wrong and what if I really need that wireless connection to be enabled, while using XMLP?

Recently, I found an answer:


“You have encountered a “safety feature”
within the lightweight (pure java) OC4J Java Messaging System (JMS)
implementation. The OC4J Java Messaging System creates a lock file to constrain
access to a specific persistent message store to the single OC4J instance that
created it.”

“The persistence lock file captures
information about the OC4J instance that owns or previously owned the
persistence store and this information includes the IP address of the owning
oc4j instance. During startup if the OC4J Java Messaging System (JMS) discovers
that a persistence store it has been configured to use has a lock file that it
doesn’t own it will abort the startup procedure to avoid data corruption.“


Opening, for instance the jms.state.lock file shows the following:


F10.1.54.115:/C:/oracle/product/10.1.0/db_1/oc4j/j2ee/home/persistence/


… where the IP-address at the beginning of the line is – coincidence or not … – my Wireless Network Connection!!! So, a change had taken place at the network level since I last started an OC4J instance, causing the OC4J not to start anymore.

The start-up problem can be resolved via the following steps:

  • Ensure that there is no running OC4J instance that is accessing the persistence store
  • Navigate to the “persistence” directory for the OC4J instance that fails to start
  • Remove the “.lock” files

When starting the XMLP OC4J instance, the *.lock files will be created again, but your OC4J will start without any problem, as they will contain the correct IP-address.


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.