Skip navigation.

Andrejus Baranovski

Syndicate content
Blog about Oracle technology
Updated: 17 min 2 sec ago

Check Box Support in ADF Query (11g R2 and 12c)

Sun, 2014-04-27 03:28
Few years passed since my previous post about check box support in ADF Query - Yes-No Check Box in Query Criteria. It was not working before, but there are improvements in the latest ADF releases. I will describe approach to handle multiple values from the same column, using check box in ADF Query. By default, you could render multiple values with choice list - but there is a way to use a group of choice lists (when column values will be known in advance).

Sample application -, implements ADF Query with a criteria to search by employee status. This status could be of three types - internal, external and consultant. All three check boxes are rendered in ADF Query, under Status group (check box with Internal status is ticked by default):

You could tick External in addition and bring both Internal and External employees:

Tick External and Consultant - this will bring different results:

Status for Internal, External and Consultant is set through Status field in Employee table:

As you can see, status is encoded through I, E and C values accordingly. This would mean, I would need to define three attributes in the VO, pointing to the same column in DB. Each attribute should handle different status. I would need to have LOV VO, to to define a check box. In order to simplify it and avoid creating three different VO's for each attribute LOV - I'm going to create only one VO with bind variable. This SQL query would always return bind variable value back - this is what I'm going to control through View Accessor:

We are done with LOV VO. There are three attributes defined in VO, based on the same EO attribute. Each attribute for different status:

Make sure to set attribute Control Type to be Check Box, before defining LOV. I'm using UI Category - EmployeeStatys, to group attributes in ADF Query:

Each status attribute is assigned with LOV. Keep in mind - each LOV is configured through different View Accessor (this is needed to pass a proper bind variable, based on a status represented by the attribute):

Here you can see an example - 'E' external flag variable is configured for View Accessor used in LOV configured for External employee status attribute:

UI groupings visible in ADF Query UI, are configured through UI Categories section in VO:

Check box for Internal status was ticked, using default value in View Criteria - 'I':

Ready-To-Use SOA/BPM Virtual Machine and Case Management Sample

Tue, 2014-04-22 12:31
There is a new excellent Virtual Box VM for the latest SOA/BPM This one is my favourite, it includes latest Oracle products for SOA/BPM and WebCenter. All is configured, fine tuned and ready to use. Read more and download from Oracle OTN site - Pre-built Virtual Machine for SOA Suite and BPM Suite 11g.

The same as in previous release of this VM, you can start each server with one click in the console window:

UI for the standard BPM Worklist application is refined in SOA/BPM release - it looks lighter and cleaner. Definitely, something users would like to use, everything can be customised if needed:

This new VM comes with a sample application for Case Management - Car Rental Sample. What I didn't liked about this sample - it doesn't implement proper ADF UI. There are two screens, first one brings a form to fill a car rental request. This is a proper ADF UI:

Press a button - Reserve Car. This will bring you to the next screen, with login dialog (this doesn't look like ADF anymore - login dialog UI reminds some plain HTML):

Provide jcooper/welcome1 credentials and login. You will be redirected to the screen with multiple tables. However, none of these tables are build with ADF:

It turns out, there is a basic inline frame included into ADF shell. Why to build separate application and include it into ADF with inline frame (while the same tables could be implemented in ADF):

Someone could get confused by this sample, and think of such design as a best practice. Or even worse - ADF not capable to implement a pair of tables. I hope this sample will be improved in the future and based fully on ADF.

Case Insensitive Search in LOV - Effective and Generic

Fri, 2014-04-18 12:50
Search in LOV dialog window, by default is not case insensitive. You could define View Criteria for LOV VO with case insensitivity and select this criteria to be displayed in LOV dialog. You could do this for one or two, may be for ten LOV's - but I'm sure you are going to get tired pretty soon. Much more effective is to implement generic solution to convert LOV search criteria to be UPPER case automatically.

I'm using sample application from my previous post about dynamic ADF BC and new dynamic ADF UI component in ADF 12c - ADF Dynamic ADF BC - Loading Multiple Instances (Nr. 100 in 2013). The same technique as described below can be applied also for design time ADF BC, across different ADF versions. Download sample application, updated for this post -

Default search dialog is case insensitive, you could test it quite easily. Try to search for lower case value, when you know there are upper case matching values - there will be no results:

SQL query is generated with a WHERE clause as it should, it is trying to search for matching values - but there are no such records in DB:

We could solve it with generic class - our custom View Object implementation class. My sample is using dynamic ADF BC, so I register this custom class programmatically with VO instance (typically you could do it through the wizard for design time ADF BC):

As I mentioned above, sample application is using ADF UI dynamic component, however the same works with regular ADF UI also - it doesn't matter:

Here is the main trick how to make search from LOV dialog to be case insensitive. You must override buildViewCriteriaClauses method in View Object implementation class. If current VO instance represents LOV (if you don't want to rely on naming convention, you could create additional View Object implementation class, intended to use only for LOV's), we should invoke setUpperColumns method applied for View Criteria. This converts entire View Criteria clause to use UPPER for both criteria item and bind variable:

Now with automatic conversion to UPPER case, try to execute the same search - you should see results:

View Criteria clause is constructed with UPPER and this is why it allows to perform case insensitive search. Of course, for runtime DB performance optimisation, you need to make sure there is functional index created for searchable columns:

The same works for any number of View Criteria items. Here I search using both attributes:

View Criteria clause contains both attributes and both are changed to use UPPER - this is perfect:

Case insensitive auto completion works as well with the technique described above. Try to type a value, existing in LOV - but with lower case (it_prog):

Such value is located and is automatically changed to use the case as it is originally stored in DB (IT_PROG):

View Criteria clause was constructed with UPPER in the case of auto completion as well as with regular search in LOV dialog:

ADF Query Design Revisited with ADF 12c Panel Drawer

Sat, 2014-04-12 12:22
My goal of this post is to take a closer look into ADF 12c and check how ADF Query layout can be optimised, using Panel Drawer component. There are several items, sample application is focusing on:

1. Panel Drawer component in ADF 12c and its usage for ADF Query

2. Declarative mode support for VO Query optimisation

3. Dynamic bindings in Page Definition for ADF Query

4. View Object query logging

Here you can download sample application - This is how UI looks initially, when Employees fragment is opened:

You should notice magnifying glass icon on the top right, this is rendered by ADF 12c Panel Drawer component. User could click on this icon and ADF Query would slide and become available, click on the same magnifying glass or anywhere else on the page - it will slide out automatically. This is really convenient, as it allows to save space on the screen - ADF Query is rendered on top of other components in a drawer:

Obviously, you could have as many panel drawers as you would prefer - this is not only for ADF Query. So, as you can see in the screenshot above, search is executed. There are only three columns in the results table - SQL query is generated accordingly with a key and the attributes for these three columns. This is a feature provided by VO declarative mode:

Move to the second accordion group and search for different value - this group displays more columns:

SQL query is different as well - it includes now all visible attributes. Such search implementation is especially useful for the cases, where many attributes must be displayed in the result set. It makes sense first to display result set with several attributes only and give user an option to see all the attributes using additional table. This would mean, initially SQL query would be lighter and it would fetch all attributes later, as in this example:

Let's check now technical details. VO is set to run in declarative mode and all the attributes (except primary key) are marked with Selected in Query = false. This allows to calculate displayed attributes on runtime, based on ADF bindings and construct SQL query accordingly:

There is one hidden gem in the sample application - generic class to provide detail logging for executed SQL queries and rows fetched. You could use it immediately in your project, without any change:

ADF Query is integrated into Panel Drawer using regular ADF Faces Show Detail Item component, there is custom magnifying glass image set:

Each accordion item, where results are displayed, is set with disclosure listener - this is needed to update current context and apply ADF Query to filter results either in Preview or Complete accordion items:

Accordion item is configured with JSF attribute, to identify itself:

Accordion item disclosure listener is updating currently opened item index, this will be used in ADF bindings - to map dynamically ADF Query binding with proper results iterator:

ADF Query in ADF Bindings is represented by Search Region. Unfortunately, Search Region property Binds doesn't work with dynamic expression. I resolved this with one trick - have defined new iterator, where Binds property is calculated dynamically (using current accordion item index). Search Region points to this this artificial iterator, and iterator in turn points to the VO instance. Both results tables are pointing two the same VO instances:

You can spot attribute definitions in the table bindings - VO declarative mode decides which attributes to include into SQL query, based on these attribute bindings defined.

MDS Seeded Customization Approach with Empty External Project

Sat, 2014-04-05 11:27
Great feature in ADF - MDS Seeded customisations support. This is particularly useful for independent software vendors, who are developing their own products on top of ADF framework. With MDS Seeded customisation, maintenance for multiple customers becomes easier. We could create customisation, instead of changing original source code - this makes it easier to maintain a product. I would like to share one important hint, related to technical architecture for MDS Seeded customisations - this will be related to the way how MDS Seeded customisations are organised and maintained. By default, you would create MDS Seeded customisation files in the original application, however this is not very clean approach. There is a way to create and keep all MDS Seeded customisation files in empty external application. I will describe in the post, how this can be achieved with a few easy steps.

If you are going to test sample application -, with integrated WLS instances in JDeveloper, make sure to read this post (it describes how to setup local MDS repository in file system) - How To Setup MDS Repository for Embedded WLS Instance.

Let's start - you can download initial version of sample ADF application from the blog post mention above. Employees form and empty Submit button:

I don't want to create any MDS Seeded customisation files inside, rather I would build and deploy ADF library out of main application:

Sample comes with special application - CustomizationApp (you can find it in the archive). This application was created to keep MDS Seeded customisation files, no other purpose. Initially, empty project was created - where ADF library was imported (the one we have just deployed):

Empty project is enabled with MDS Seeded customisation support:

Restart JDeveloper in customisation mode - so we could create some customisations for the content from imported ADF library:

If MDS Seeded customisation mode was successfully applied, you should see some special icon, next to the application name. Choose 'Show Libraries' to see a list of libraries - so, we could see contents from imported ADF library:

All attached libraries will be displayed, you should locate our ADF library. Expand it and you should see application packaging:

We could apply several customisations now. Let's open Employees VO and define View Criteria (filter employees by salary >= 1000). This customisation will be stored inside our empty project:

There will be a change in AM - View Criteria will be applied for VO instance:

We could go and review XML files for applied MDS Seeded customisations. There is one for VO and AM. XML for AM contains delta information about applied View Criteria for VO instance:

One more customisation on UI level now - drag and drop Commit operation for Submit button:

This change creates two additional XML files with MDS Seeded customisations - for JSF fragment and Page Definition:

You must define MDS Seeded customisations deployment profile (MAR) for application with empty project (containing XML's):

Development is completed, now will be a last bit - deployment. Make sure WLS is started (you should start it separately, if you want to test MAR profile deployment):

Go ahead and deploy main application first - you should get a list of MDS repositories (see a hint how to define local test repository in the blog post mentioned above):

Once main application was deployed, you can apply MDS Seeded customisations and export them through a MAR file from external application:

You should see main application name in the wizard, MDS Seeded customisations will be applied for this application:

All the changes applied through MDS Seeded customisations, will be visible in the log:

Good point - there is no need to restart main application, after MDS Seeded customisation changes were applied. Here you can see, original application with changes as per above:

If applied changes should be removed, this could be simply removed from MDS Seeded customisation XML file and re-applied. Main advantage of this approach - no need to store XML files with MDS Seeded customisations inside original project, we could keep them outside.