QA-4-Apex: the sequel

In one of my previous blogs, I already told we grouped the different QA-checks-you-want-to-perform in a single Apex application.

A special attention point is that this application needs to be imported in the workspace where the application resides that you want to control. This is due to the way the apex_ repository views are designed: you only see those applications where the different schemas assigned to the workspace have access to.

Since the last blog, we changed a little bit our approach. At that time, we had the idea tho group all related qa-checks in one UNION’ed view, containing ALL the queries for the different controls.

The main disadvantage of this implementation is that each time you want to add an extra check, you need to modify the view; this means doing some DDL.

Therefore, we decided to store the SQL-statements in a separate column related to each different control. Following screenshot, shows you the page that is used to add/modify a pre-defined control:


The sample query is used to check whether the Report Column Headings are always aligned the same way: Left, Right or Center. As you can see we do not hardcode one of those possible options in the where clause; but retrieve the standard-alignment-for-the-columns via a function call (Pck$QA_APEX_Standards.heading_alignment).

Therefore we created a separate table where we define all kind of parameters of which the value can differ from applicaton to application. The following screenshot shows the page where the parameter “heading_alignment” is defined with his default value.


If you want to differ from this default value for a given application, we have foreseen the possibility (via a second table), to store for a specific application (GWI in the example) the standard-value we agreed on with our end-users. This permits to have different standards for different apex-applications in the same workspace.

I can proudly present to you … The One and Only Oracle Fusion Regional Director for Belgium

I can proudly present to you … the finest … the greatest … the smartest … Oracle Fusion MiddleWare Regional Director for Belgium !

To be honnest I can’t be modest because I’m in seventh heaven wright now ;o)

Secretly – without informing my parents, my firm, my husband … because it’s so important to me – I’ve send a nomination form to Oracle regarding becoming an ‘Oracle Fusion Regional Director’.

After waiting for an enormous long time – 3 weeks, but that feels like ages when you’re awaiting acceptance – I’ve got the big news:

I am pleased to inform you that we have reviewed and accept your nomination for Oracle Fusion Middleware Regional Director.

What I felt when I received this e-mail was … ‘If you’re happy and you know it clap your hands …’ I’m clapping away, clap clap clapperdiclap … in other words an appreciation and acknowledgement of the commitment of the non-Oracle consultants which spend a lot of time promoting the Oracle Products and Services and helping others on OTN, such as myself.

As we would say in Belgium ‘Ik ben in de 7de hemel’ / ‘I’m in seventh heaven’ !!!

Dba: Capturing a time frame from the ALERT.LOG

A thing I did not know about my database….

The alert.log file chronologically records messages and errors about database operations such as startups and shutdowns, internal errors (ora-00600), links to trace files and much more.
This often makes the alert.log-file, the primary file that needs to be consulted in order to identify and solve issues concerning your database.

Unfortunately there’s no default “auto-cleanup” of this file and so the file can grow to an unmanageable size (e.g. impossible to edit using VI) and that makes it hard to search for message within a certain time frame.
By using Enterprise Manager, it is very easy to browse through this alert.log file and to capture/select only those message you’re interested in by filtering on a start and end date.


An example:

STEP 1: Connect to the EM Database Console:

STEP 2: Click the “Alert Log Content” hyperlink (on the Home-tab)


. . .

STEP 3: Fill in search criteria and push “Go”-button

STEP 4: Result

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…)

Invoke an ESB Service in your User Interface and display the response of this service

How can you bind an ESB Service in your User Interface and more specifically how can I call an ESB Routing Service and get the Result back in my UI?

Let’s say I’m developing a JSF Application and I want to use my existing ESB Services for my binding layer. The way you bind a webservice inside a JSF Application can be resolved in 2 different ways:

  • Create a Webservice Data Control
  • Create a Web Service Proxy and call the endpoint of the webservice with a valid parameter using for example a backing bean

OK, now I’m able to call an ESB Service but how can I get the response of this service back into my User Interface …

You need to create a two-way ESB Routing Service, meaning you need to define a request- and response-operation in your routing service.
E.g. You’re invoking a select on a table and the resultset needs to be displayed in your UI. In other words you want to show a list of employees in your user interface, and this list is bound to an esb service.

The steps you need to perform to get this working:

  • Create a new ESB Project
  • Define a system/group for you Esb Services (in this way it isn’t published to the default system)
  • Create a Database Adapter where you define a select statement on a given table
  • Define a new Routing Service where you define the request parameter, being the selectInputParameters-tag which is provided in the xsd that was generated for your DB Adapter and the reply parameter of the Routing Service is the collection returned by this same xsd.
  • Publish the ESB Service to your ESB Server

Now you can test this ESB Service to see that the resultset of the database adapter is returned by your routing service.

The next step is to define a UI where we can call this ESB Service, the steps you need to perform:

  • Create a new project and define a new JSF-page in this project
  • Create a Web Service Data Control ‘which can be found in the ‘Business Tier’ categorie of the ‘new gallery’
  • In step 1 you need to define a meaningfull name for the web service and in the URL you need to copy/paste the ‘Concrete WSDL URL’ of your routing service which is available through the ESB Control’. Then click browse to get the methods/operations available in the ESB Service and click next
  • In step 2 you need to choose the operation ‘execute’ and add it to the ‘selected’ area, click next
  • Click finish

Now you’re almost there, you’ve defined the web service data control which let’s you bind your UI to your ESB Service. The last thing you need to do now is to create a jsf-page and drag and drop your object inside the jsf-page:

  • Create a JSF-page
  • Drag-and-drop a PanelPage component inside the JSF page to have a nice lay-out
  • Go to the Control Palette and choose the ‘employees’-collection, your resulting objects of the db adapter, and drag-and-drop it into your jsf page.
  • Choose a table-component to have a nice view of your data

And now it’s show time … run the page in your embedded container and have a look at how esb services can be seamlessly integrated into your UI.