ADF BC: Fun with Bind Parameters – Part One – Complex Order By clauses

Lucas Jellema 1
0 0
Read Time:2 Minute, 55 Second

Bind parameters (:paramName) are typically used in the Where clause of SQL queries underlying ADF BC ViewObjects. However, there is no rule that they can only occur in the where-clause. Any part of a query can be made a little dynamic through the use of bind-parameters. In this article an example of how we use a bind-parameters in an order by clause inside a ViewObject, and how we can leverage this bind-parameter from our (JHeadstart generated) ADF Faces user interface to choose from various wildy varying order by clauses.

In this article, I will demonstrate how we can order a list of employees (yes, table EMP again) by various Order By clauses, by selecting the appropriate option from the Dropdown list.




1. Add an Order By clause to the ViewObject, using a bindparameter:

when :orderBy = 1
then (select count(*) from emp sub where sub.mgr = emp.empno)
when :orderBy=2
then extract(month from hiredate)
when :orderBy=3
then SalaryRankInDept
when :orderBy=4
then (select count(*) from emp colleagues where colleagues.job = job)

Here I have specified four different Order By cases: 

  • order by the number of subordinates an employee manages
  • order by the month in which an employee is hired
  • order by the position an employee has in the ranking by salary in his own department
  • order by the popularity of the job an employee has (the number of colleagues in the same job)


2. Use the bind parameter in some non-intrusive way in the Where clause (it seems that we can only use the VO.setWhereClauseParams() method for bindparameters that actually appear in the WHERE clause:

nvl(:orderBy,0) <> -1

3. Define a bind-parameter for the ViewObject of type Number

This completes the ViewObject. Now we turn our attention to the ViewController project. (I am assuming a project that has already been JHeadstart enabled using the JHeadstart wizard).

4. Create a Group of type Table, based on the EmpView1 ViewObject usage. 

5. Create a domain OrderEmployeesBy with all the order by options we want to support

6. Create a Non-Bound Item (not bound to a Model Attribute) of display type Dropdownlist, associated with the new OrderEmployeesBy domain.

7. Configure a Managed Bean that will hold the value of the Order By item – note: we already referred to that bean in the Value property of the OrderBy item!

The class is very simple:

package nl.amis.hrm.view;

public class OrderByBean {

private Integer orderBy= new Integer(1);
public OrderByBean() {

public void setOrderBy(Integer orderBy) {
this.orderBy = orderBy;

public Integer getOrderBy() {
return orderBy;

8. Have one of the other attributes depend on OrderBy (in order have OrderBy have its autoSubmit attribute set to true)


9. After generating the application, change the table element in the EmployeesTable.jspx page: set its partialTriggers attribute to include the OrderBy item; this ensures that whenever the dropdownlist Order By? changes, the table is refreshed.


When we run the application, we will see a table with the first 10 employees. By choosing a different value in the Order By dropdownlist, we will cause the table to refresh, with the employees listed in the order we selected in the dropdownlist.

In this case, we see the best paid employees from each department listed first, followed by the runners-up from each department, followed by those employees who come third etc.


Download JDeveloper Application with the projects described in this article:

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

One thought on “ADF BC: Fun with Bind Parameters – Part One – Complex Order By clauses

  1. Hi
    Pretty old blog but good and useful to me. Could you please explain how the managed bean is updating the view object bind variables? I am using 11g now and couldn’t understand the use of managed bean in this blog.

Comments are closed.

Next Post

ADF BC and Faces: Fun with Bind Parameters Part Two - Locale specific database querying from the ViewObjects

In this article I would like to demonstrate another unexpected usage of ADF BC bind-parameters. In a previous entry I argued that bind parameters are typically seen as good only for the where-clause of an SQL query underlying a ViewObject. In that entry I showed using a bind parameter to […]
%d bloggers like this: