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

Apex: Caveat PK column name length

It is highly encouraged in Apex to use system generated, single primary key columns whenever possible.

When choosing multi-column, composed primary keys, you quickly get in trouble when dealing with the standard MRU Processes. This is limited to handling tables with at most a 2 column primary key.

So, it is a good practice to work with auto-generated, sequence-based PK’s, on 1 column.

There is one caveat that you should be aware of.

Within Oracle, the maximum length of a column name is 30 characters.
However, when you plan to build tabular forms on your tables, the length of the primary key column name may not exceed 22 characters.

Automatically Backing Up Your Apex Application(s)

Normally you can backup a specific ApEx Application by taking a manual export via the standard Apex interface. You can rename the export file by putting a date/hour notion in the name and copy the renamed file on a drive that is included in your general corporate backup process.

This is a laborious work … And once in while you forget to do it…

So, it would be nice to automate this process without requiring a manual export from the Web interface.

Since ApEx 2.2 you get a java-based export utility for free that allows us to export ApEx applications from the command-line.

When you download Oracle Application Express (here) and unzip the file , you will notice that there is an extra subdirectory named …/Utilities. This contains 2 java programs and a small readme-file explaining how to use these command-line export utilities.

The first program is APEXExport. You can easily integrate this APEXExport-program in a script and schedule this script on a daily base.

Following is an example of backup-bat file that exports for a windows environment a given ApEx application (by application id), rename it by putting a date notion in the filename and move it to another (backup) directory. This bat-file can be scheduled in windows using the AT command.

You should adapt the values in blue to your local situation.

@echo off
@REM *****************************************************************
@REM * File: backup.bat
@REM * Purpose : Export a given oracle ApEx application
@REM *****************************************************************
@if ‘%theDrive%’ == ” set theDrive=d:
@if ‘%theOraclePath%’ == ” Set theOraclePath=C:\Oracle\10gR2\HTMLDB\jdbc\lib\classes12.jar
@if ‘%theUtilityPath%’ == ” set theUtilityPath=d:\iaf\utilities
@if ‘%theDbHost%’ == ” set theDbHost=wxpp-vhoofe:1521:orcl
@if ‘%theUser%’ == ” set theUser=iaf
@if ‘%thePwd%’ == ” set thePwd=iaf
@if ‘%theAppId%’ == ” set theAppId=131
@if ‘%theBckDir%’ == ” set theBckDir=D:\Iaf\backups\apex\
@REM *****************************************************************
@REM *****************************************************************
@REM => change current directory
@REM *****************************************************************
cd %the
@REM *****************************************************************
@REM => do the export
@REM *****************************************************************

set CLASSPATH=%CLASSPATH%;%theOraclePath%;%theUtilityPath%
java oracle.apex.APEXExport -db %theDbHost% -user %theUser% -password %thePwd% -applicationid %theAppId%

@REM *****************************************************************
@REM => Rename and move the file
@REM *****************************************************************
@for /F “tokens=2,3,4 delims=/ ” %%i in (‘date /t’) do set DateStamp=%%k%%j%%i
@for /F “tokens=1,2,3 delims=:,. ” %%i in (‘time /t’) do set TimeStamp=%%i%%j%%k
@SET BackupFile=%theBckDir%f_%theAppId%_%DateStamp%_%TimeStamp%.sql
move f%theAppId%.sql %BackupFile%
@REM***** End BAT File ************************************************

The yellow part contains the actual call to the java export program. For explanation of all the available parameters, you can verify the readme-file.

The second java program is called APEXExportSplitter and it can be used to split an export file into separate SQL scripts. This can be useful for example when you want to restore a discrete component/element of your ApEx application.

Give it a try

ApEx Sample Application … Great … But …

Creating the sample application by default is a nice idea within Apex.
It is simple. You define a new workspace, and you get if for free … Great …

When you are a newbie, this application gives you a good idea of the possibilities of the tool.

But after a while it gets annoying to remove each time you make a new workspace that application and especially all those default demo_xxx tables, created in your oracle-schema that owns that workspace.

Did you know that you can suppress that automatic creation of this demonstration application ?

You need to login as Apex Administrator.

Navigate to Manage Service / Manage Environment Settings

In the region “Application Development”, you can switch off the creation of the demonstration objects in new workspaces.

When you still want to play with this sample application afterwards, you can always use the “Application Creation Wizard”. One of the options there is the creation of the demonstration application.

Een Oracle Consultant op een Microsoft project

Meer dan twee jaar geleden (mei 2004) werd ik gevraagd om mee van start te gaan in een (groot) Microsoft (.NET ) project. Nochtans was ik voordien vooral actief geweest in de Oracle wereld. Toch leek het voorstel mij wel aanlokkelijk omdat ik de gelegenheid kreeg met een andere RDBMS te werken; toen moest nog gekozen worden tussen IBM’s DB2-Informix of Yukon (code naam voor SQL Server 2005 bèta). Maar ik heb vooral toegehapt omdat men expliciet op zoek was naar een “Data Architect”, en vermits die titel toen nog op mijn business kaartje stond, was de keuze snel gemaakt.
Nu heb ik geen business kaartje meer …

“Data Architect” … wat is dat ? Persoonlijk vind ik dat elk data-centric project zo iemand kan gebruiken.

Echte, éénduidige definities hiervoor vind je wel niet direct terug. En iedereen zal daar wel zijn eigen interpretatie aan geven. Het is dan ook mijn bedoeling om de komende weken een poging te doen uit te leggen wat ik onder DA versta, welke rol voor hem weggelegd is en hoe dit ingevuld werd op dat grote Microsoft project.
Daarnaast hoop ik ook ergens iets te vertellen over de opvallende verschillen die bestaan tussen het ontwikkelen van een applicatie die moet draaien tegen een Oracle database en het werken in SQL Server.

Als afsluiter wil ik alvast meegeven dat je een Data Architect nooit als DBA mag aanspreken, want dat vindt hij kwetsend ;-)

(wordt vervolgd)

AYTS: A visit to Oracle HQ

Since 3 years, Oracle Belgium organises for their partners a technical contest. They call it:
Are You The Smartest ?” (AYTS).

The objective is to motivate the consultants-of-those-partners to learn new features / tools in the Oracle product portfolio by means of a contest. Oracle presales consultants give a series of technical presentations on new technologies. Every session (except the first one) starts with an online exam about the previous session. The consultants of every partner with the best results on those exams can win a trip to the States.

This year, a group of 10 “Oracle Smartest” had the chance to make this trip to the US. I was one of them …

The official part of that trip is a visit to the Oracle Headquarters. We stayed at a hotel in San Francisco. Early in the morning, an Oracle chauffeur in an Oracle limousine picked us up for a 30 minutes ride to Redwood where the famous HQ buildings are located.
We were welcomed by a small breakfast-buffet, in order to survive the next four hours when three different presenters gave an explanation on specific topics.

In a first presentation, Mark Drake, the Senior Product Manager for XML Technologies, gave a brief overview on XML DB. With the Oracle solution, you have the possibility to treat relational data in an XML way or approach XML documents via SQL. In addition, from release 10gR2 on, Oracle implemented as the first vendor XQuery facilities in the database. The most important thing that I remember from this session is this quote by Mark: “We are 5 years ahead on our competitors; and they will need 3 years to notice it.

The second presenter was Scott Howley, Senior Director and Product Manager. He gave an Overview on Oracle Fusion Middleware.
15 Years ago, as an Oracle developer, you could only use Oracle Developer (Forms and Reports) to get data in and out of your database. Ever since, Oracle never stopped his efforts on developing numerous technologies that reside between the application and the database. Nowadays they branded this entire family of Oracle’s middleware products as “Oracle Fusion Middleware”, their answer on the SOA claims by the market.
In one hour, Scott tried to explain, in a very enthusiastic way, each component, too much to list here. Multiple times Scott emphasized that central in all their solutions is the conformance to open standards. A good example of this standardisation is Oracle’s BPEL solution. You can use Jdeveloper or Eclipse as IDE to develop the BPEL process and you can deploy it against multiple Application Servers. Unfortunately, some tools do not fit in this picture of openness and standards … Yes, indeed, the good-old Oracle Forms and Oracle Designer. Do I need to say more …?

In a final session, Tim Dexter — Group Manager, XML Publisher Product Management — gave an introduction and demonstration of XML Publisher. This product was originally part of the E-Business Suite (aka “Oracle Apps”), now it is available as a separate product. XML Publisher was an answer on all the problems the development team had with maintaining the >1000 business reports, in different languages and in different versions. The main idea is that the extraction of the data (XML), the layout of the document and the translations are three different components, independent of each other.
For producing business reports and documents, this reporting solution will probably replace Oracle Reports. The integration with MS Word and Excel seems very promising.
On the other hand, there is still a lot of confusion about the pricing and licensing of the product — it is still very expensive. And furthermore, there is also some overlap with other BI tools like Discoverer and the Siebel BI solution — I read somewhere that they want to rename it to BI Publisher — Nevertheless, Oracle still has some home work to do in positioning each of these products.

As one could expect from interesting sessions, they did not end at the scheduled time. Consequently, the chef had to do some overwork. Nevertheless, no complaints, we got a delightful lunch in the Executive Dining Room.

Our visit ended with a small Campus tour. We walked through Oracle’s own Conference Centre, visited the enormous fitness centre and admired the well-known Oracle Buildings. You can find another picture here.

This was the end of the official part of our trip to the states.

Conclusively, when looking back, it was a pity we did not see “God” him self. Nevertheless, the least we can say is that we used his Limo.

I can recommend everyone to participate at the AYTS contest of next year…at least the Belgian Oracle consultants.