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.

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.

Apex: Translating Messages Used for Reports

Oracle Application Express is translated into German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese. If your application uses a language that is not among the ten languages into which Oracle Application Express is translated, you need to translate messages displayed by the Application Express reporting engine.

For example, if you develop a Dutch application and want to include report messages (such as pagination) in Dutch you need to translate the strings used in messages displayed in reports.

As example we will replace the word ‘Next‘ displayed in a report navigation with the dutch word ‘Volgende‘. In order to do this we will have to perform the next actions:

1. Go To your application and perform the next actions:

A. Click Shared Components.

B. CLick Edit Globalization Attributes
C. Click Message Translation (right on the page)

2. On the Translate Messages page, click Create.

3. On Create/Edit Text Message, specify the following

A. Name – Enter the name of each report message that needs to be translated.
The name for the ‘Next’ message is ‘PAGINATION.NEXT‘. More info about these names can be found here.

B. Language – Select the language for which the message would be used

C. Text – Enter the text to be returned when the text message is called. We will set the word ‘Volgende‘ here.

4. Click Create.

5. Run your report.