Apex: Build Your own QA-system by Fully Exploiting the Apex Dictionary Views

A few weeks ago we started a new APEX project.

One of the typical tasks at the beginning of a new project is agree on some formal development aspects; you need to formalize some guidelines to guarantee consistency throughout the Apex application.

The development team needs to agree on questions like:

  • Which is the standard reports region Template ?
  • Which pagination schema will we use in the reports ?
  • Which format for dates will be used ?

We use a standard form like displayed in following figure:

Standards and guidelines are good, but how can you control that they are followed ?

I was looking for a way to control whether the developers follow those formal conventions we agreed on. We can do some code review, but that is a very time consuming tasks.
I needed a more automated approach.

Since APEX stores all his metadata in tables, it must be possible to query and check that data. So I took a closer look to all those Apex Dictionary Views that are exposed since Version 2.2.

For example to see whether there exists regions that are not based on the standard template “Reports Region”, we can query the apex_application_page_regions view and look at the template column. To check whether the pagination scheme is set on every page to “Row Ranges X to Y of Z (with pagination)”, query the pagination_scheme column in the same view. You can UNION those two queries and you get something like in following figure:

SELECT xapg.page_id, xapr.page_name, xapr.region_name,
xapr.source_type,
xapr.template as wrong_value,
xapp.application_id as apex_app_id
FROM apex_application_page_regions xapr
JOIN apex_application_pages xapg
ON (xapg.page_id = xapr.page_id AND
xapg.application_id = xapr.application_id)
JOIN apex_applications xapp
ON xapp.application_id = xapg.application_id
WHERE xapg.page_id 0 -- exclude page zero
AND source_type IN ('Report','Tabular Form', 'PL/SQL')
AND template not like ‘Reports Region’
UNION
SELECT xapg.page_id, xapr.page_name, xapr.region_name,
xapr.source_type,
xapr.pagination_scheme as wrong_value,
xapp.application_id as apex_app_id
FROM apex_application_page_regions xapr
JOIN apex_application_pages xapg
ON (xapg.page_id = xapr.page_id AND
xapg.application_id = xapr.application_id)
JOIN apex_applications xapp
ON xapp.application_id = xapg.application_id
WHERE xapg.page_id 0 -- exclude page zero
AND source_type IN ('Report','Tabular Form', 'PL/SQL')
AND xapr.pagination_scheme
‘Row Ranges X to Y of Z (with pagination)’

This way, you can assemble all different kind of queries that check on the different conventions your development team decided on.

One special thing, you should always keep in mind when you query those apex-views is that you must run those queries via the owing schema of your application. Those views are built that way that you can only see metadata of those applications where your user has access rights to.

This is the base of my tailor-made quality system. In one of my following blogs I will elaborate this basic idea. The next step of course is to build on top of this queries some reporting interface, …with Apex of course …

Oracle Apex in the IT-World

Today two different colleagues sent me — almost at the same time — a mail, each with a different URL, referring to articles on the web dealing with the new release of Apex 3.0.

This proves that one starts talking about Oracle Application Express outside the (small ?) community of Apex adepts and bloggers.

The first one is published at ITworld.com and is quoting Mike Hichwa, vice president of software development, already talking about the big ambitions coming with release 3.1, which is likely to appear before year-end !

The second article can be read at Nasdaq.com. Among others, it contains some testimonials by organisations explaining why they decided to choose Apex as development platform.

See also the blog by Scott Spendoline “Apex 3.0 in the news“.

Apex, a 4GL-for-the-Web…

Until now, Oracle JDeveloper (with ADF) was always positioned by Oracle Corporation as the successor for Oracle Forms.

Now, with the release of Apex 3.0, it is the first time, — as far as I know — that Oracle Corp. considers ‘openly‘ Apex as an alternative for Forms.

On the restyled Oracle Apex Home on OTN, you can find a small comparison between both tools: both declarative, SQL & PL/SQL driven, … Read the rest of the article here.

Especially, pay attention to the “development status” of both products: Oracle forms is in ‘maintenance mode‘, while Apex is in Active Development‘!

Apex Evangelisation To Do …

As announced in one of my previous blogs our Apex seminar took place yesterday.

More than 50 people registered and came over to the offices at Oracle Belgium to listen to the different presentations, case studies and testimonials. The audience consisted of people with very different profiles: developers, project leaders, managers and decision-makers. We tried to find an equilibrium between the more technical demo’s and the real show cases.


It was not that easy, but – if we may believe the evaluationsheets and the reactions after the sessions – I think we met the expectations of all the attendees.

For those who could not be present, we provide a link where you can download a zip-file with the presentations.

One of the things that striked me the most was the fact that Apex is not yet that well-known as I suspected. We, at iAdvise, are now already working with Apex for almost 2 years; we did different projects. On the internet you see that the community is growing (e.g. Apex on OTN), but not yet in Belgium… Within the audience, there was only two (!) persons (of which one then still of the Netherlands !) who really worked with the tool; in the after-talk one of the people said that she had never heard of Apex before…

So we still have to do some Apex-evangelisation

There is however one common problem where a lot of people struggle with. The often heard remark was: ”We are now working in the traditional Oracle forms, often still in C/S mode. Which direction should we take? Web-forms, knowing that this technology does not have a real future anymore, Jdeveloper with ADF or Oracle Application Express”.

It is my personal believe that Apex is a very good alternative for Oracle Forms, maybe not for all applications, but Apex is also much-much more than an alternative for excel and ms-access (i.e. the way Oracle originally positioned the tool).

I am curious what other people think about this. Feel free to post your comments …

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 !

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…