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,
FROM wwv_flow_file_objects$ o

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.

Apex: Using a Date Format Mask at Application Level

Every Apex application contains a lot of report pages with date columns and form pages with date fields. For all these items you can specify a date format mask. Never asked yourself if it is possible to define that format mask only once at a central place in your application?

With PICK_DATE_FORMAT_MASK you can. This is a user defined substitution string at application level. It is the best way to keep the dates consistent throughout your application.

  1. How to create the PICK_DATE_FORMAT_MASK substitution string?

    Creating the PICK_DATE_FORMAT_MASK substitution string is done on the edit application attributes page. So, navigate to “Edit Attributes” (application level) à “Definition” and scroll down to the “Substitutions” region. Enter “PICK_DATE_FORMAT_MASK” in the “Substitution String” column and enter your date format mask (here “DD-MM-YYYY”) in the “Substitution Value” column. Press “Apply Changes”.

  2. How to use your application format mask on a report page?

    Suppose we have a report page presenting table “EMP” with column “HIREDATE”. For that “HIREDATE” column we want to use our application level format mask.

    Go to the “Column Attributes” page of the column “HIREDATE” and go the “Column Formatting” region. For “Number/Date Format” enter “&PICK_DATE_FORMAT_MASK.”. Press “Apply Changes”.

  3. How to use your application format mask on a form page?

    Suppose we have a form page to create or edit a row from table “EMP”, with also the “HIREDATE” column. For that “HIREDATE” column we want to use a date picker with our application level format mask.

    Go to the “Edit Page Item” page of the item on “HIREDATE” (here “P2_HIREDATE”) and go the “Name” region. For “Display As” choose “Date Picker (use application format mask)”. Press “Apply Changes”.

    This is the result when we run the form page:

  4. Other uses of the PICK_DATE_FORMAT_MASK substitution string

    Formatting data while selecting it from the database can be performed by including the PICK_DATE_FORMAT_MASK in a TO_CHAR function:

    TO_CHAR ( hiredate, :PICK_DATE_FORMAT_MASK )

    Converting the text presentation of a date item to a date using the correct format mask can also be done by using the PICK_DATE_FORMAT_MASK in the TO_DATE function:


Apex: Deploy Your Multilingual application in a production environment.
One of the great pro’s of Apex is the ease to put an application from a development or test environment into a production environment. You take an application export at the one site, and you do an import at the other site.

When you have to deal with a multilingual application, there are some extra steps to be aware of.

Everything related to translating an Apex-application can be found at “Shared Components: Globalization / Translate Application”. There you see the 6 different steps that are necessary to translate your application in another language.

A first thing to know is that an application has always a primary language. This language is defined via the “Edit Globalization Attributes” on the Attributes pages for the application.

In our case we mapped this primary language application (with code ‘en’ and APP_ID = 144) to two other languages:

  1. One for language-code ‘nl’ (= dutch) => APP_ID = 145
  2. One for language-code ‘fr’ (= french) => APP_ID = 146

The strange thing here, is that you have to assign your self an application id for the “translated application”, Apex does not propose anything. Of course the APP_ID is unique, so the chosen APP_ID may NOT exist yet in your apex environment.

This means that after step 1, you end up with 3 different applications within your workspace. But in the application builder you only see the primary, master application (144). The two other ones are not visible as stand-alone applications.

Suppose you have finished all the other necessary steps, you have tested the applications in the 3 different languages, and you want to deploy those three applications in your production environments. How do you proceed from here?
In our example we deploy from development to production.

a) In development: Take an export of the primary application (144)

b) In development: Seed and export the translation text of your application into a translation file (.xlf).

So, the xliff file and the application export file (step 1) should be based on the same metadata. You may not change translation-sensitive stuff anymore in you application once you have generated the xml-file. Otherwise you need to restart the translation process.

In our case we have a xml file for the dutch and one for the french translations. During the development process you should have translated those two files where for every source element, you entered a translation in the target element. Those translated xlf-files you need further on.

c) In production: Import the application (144)

You will notice, that, though you only imported application 144, the mappings with the two other ‘translated’ applications (145 and 146) are automatically created.

d) In production: Seed and export the translation text.

This is the same as in step b), but now in the production environment.
You really need to do this step. If not, the next step will not work !
I assume that apex need to prepare his internal metadata, necessary for the further translation process.
So again, you will have two xliff-files, but you don’t use them further on !

e) In production: Do step 4 of the standard translation process (via Shared Components) and apply the xliff files you have created and translated in step b). Publish this uploaded translations and your application is now available in three different languages.

Apex: How to Disable a Text Area ?

Every application contains pages where the data should be displayed in read-only mode.

For a normal text item, you can declare a field as “Display as Text Item (does not save state)” and the value is shown as HTML
(see Text 1).

For a multi-line text area there is not a similar option.You can leave it as such by defining it as “Textarea (auto-height)”
(see Text 2).

This can be confusing because the enduser can edit the field and have the impression that he may change the content.

Another possibility is to set on the Element-level for that textarea-field the HTML Form Element Attributes to “disabled“.

The disadvantage of this solution is that the scrollbar disappears; so, when you are dealing with a large textextract, you may not see the complete content (see Text3).

Javascript can help us to resolve this problem. By defining the two following steps, you can scroll within the textarea, but you may not change the text
(see Text 4):

  1. Include following lines of code on the page HTML header:

  2. Add a call to this javascript function in the Region Footer of the region where the textarea belongs to, specifying the item you want to disable