ApEx Training in Belgium

Following the recent success of their ApeX seminar, iAdvise is organizing a second training course for Oracle Application Express 3.0. The course will be held 19, 20 and 21 of June.

If non-Dutch speaking people attend then iAdvise will give the course in English, the course will cost you 945 € (warm lunch included). You can register here.

The course will be a hands-on training where at the end people will be able to build and deploy a small project. You can contact iAdvise on info@iadvise.be or visit their website.

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 …