Apex: Our QA-application gets more and more substantial …

In my previous post I emphasized the power of the Apex dictionary views.

On this matter, have also a look at the very complete presentation of Patrick Wolf.

In the meanwhile, we further elaborated our idea of building a custom Apex application that should help in doing quality checks on coding standards and guidelines.

We gathered the different checks that we wanted to perform in different groups. For each group, we have built a view consisting of different UNIONed selects as illustrated in my previous blog.

This is a list of groups we distinguish so far:

  • APR => Application Page Regions (QA_APEX_APP_PAGE_REGIONS)
  • APRC => Application Page Rep Columns (QA_APEX_APP_PAGE_RPT_COLS)
  • API => Application Page Items (QA_APEX_APP_PAGE_ITEMS)
  • APP => Application Page Processes (QA_APEX_APP_PAGE_PROC)
  • APV => Application Page Validations (QA_APEX_APP_PAGE_VAL)

Within each group, we defined one or more checks, corresponding with a specific select against the apex repository. Each check we gave a short identifying code.

  • APR-001 = Wrong Reports Region Template
  • APR-002 = Wrong no_data_found_message
  • APR-003 = Wrong maximum_rows_to_query
  • APR-004 = Wrong pagination_scheme
  • APR-005 = Wrong enable_csv_output
  • APR-006 = Wrong report_null_value_as
  • APR-007 = No ORDER BY – No Dflt sort sequence
  • API-001 = Wrong date format in Date Picker
  • API-002 = Date column NOT based on Date Picker
  • APP-001 = Verify (Length) PL SQL Source
  • APP-002 = No Process Error Message
  • APV-001 = Wrong error_display_location
  • APRC-001 = Wrong date format
  • APRC-002 = Wrong Heading Alignment

After having defined the different checks and their corresponding query, the only thing we still needed to do was finding a way to give the developer or code reviewer an easy interface to perform those checks for a specific Apex application.

The following figure shows you how we finally visualized those checks:

In this screenshot, you can see for example which date item on which page and region does not yet correspond with the date format we decided on at the start of the development.

We also added an export to Csv. Like Carl Backstrom suggested, we could extract easily this data to PDF by using the PDF-Printing facilities available since Apex 3.0.

We have the impression that this QA-application can be extended and ameliorated in different ways. We are convinced that it have a lot of potential. For example, for the Apex project we are currently working on, we needed to present a global Quality Plan for the project. In that document we could refer to this QA-application as one of the ways to guarantee the quality of the application that we have to deliver within a few months.

(to be continued…)

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 …