ADF BC – Fun with Bind Parameters Part Four – Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter

1

In this article on bind parameter usage in ADF Business Components, we will discuss a feature that I recently found out about and that I think offers a lot of potential. Unfortunately I do not think I quite grasp all the possibilities yet. However, since my understanding is enough to write a nice article, that is exactly what I will do.

The core of the realization I had is that the Bind Parameter(s) defined through a View Link can also be used in the where clause of the Target (details) View Object, as long as that View Object is always queried in the context of the Source (Master). Or, to put it more precisely, the values of bind parameters in a ViewObject can be provided in several ways, one of which is through a ViewLink.

In this article I will show a simple example of how is form of bind parameter value injection can be (ab)used. I am looking for more serious applications.

I have created a ViewObject  called CannedEmployeeSelectionsView. The query for this read-only ViewObject is defined as follows:

select 'All Salesmen' Label
,      1              Value
from   dual
union
select 'Hired after 1980'
,      2
from   dual
union
select 'Top 5 Earners'
,      3
from   dual
union
select 'Management'
,      4
from   dual

It basically returns the labels for four pre-defined queries on the Emp table, each one identified by an integer.

Next I create a second ViewObject, an Updatable VO on the Emp Entity based on the EMP table, called EmployeesSelectionView. The interesting part of this ViewObject is the where clause:

:Bind_Value = 1 and job='SALESMAN'
or :Bind_Value = 2 and extract(year from hiredate) > 1980
or :Bind_Value=3 and sal >= (select sal from (select sal, row_number() over (order by sal desc) salrank from emp) where salrank = 5)
or :Bind_Value = 4 and job in ('MANAGER','PRESIDENT')

Note: the bind parameter :Bind_Value is not defined on this ViewObject. It will be injected from the ViewLink we are about to create between these two ViewObjects.

This ViewLink has CannedEmployeeSelectionsView as Source (master) and EmployeesSelectionView as Target (detail). The where-clause for the ViewLink is essentially meaningless:

:Bind_Value >0

However, by defining the where-clause as based on the Value attribute, we ensure that a Bind Parameter called :Bind_Value is defined and will be set by the ADF BC framework. Since we started this article by concluding that bind parameters defined on the View Link can also be used in the Target (Details) ViewObject, we have now made sure that the value of :Bind_Value in the where clause of the VO  EmployeesSelectionView is provided when we access EmployeesSelectionView in the context of CannedEmployeeSelectionsView.

Let’s try this out in the ADF BC Tester, after we have defined the proper Data Model for the Application Module:

Running the Tester gives us:

and

OK, just for kicks, let’s quickly develop an ADF Faces page based on these two view objects. Because it is so simple:

1. create a New JSF JSP page

2. locate the EmployeesSelectionView1 in the Data Control Palette under the CannedEmployeesSelectionsView1

3. and drag & drop it to the new page, as Master Table, Detail Table

4. run the page

and switch to another pre-canned query:

 

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Lucas, you can avoid the issue of having to use the view object only in the context of the master by defining a named bind variable in the destination view object of exactly the same name as the view link bind variable. For example, in the above article you’d define a named bind variable called Bind_Value. The allows you to give it a data type and perhaps more importantly a default value (so that it can have some meaningful usage outside of the context of the master view object instance.)