Skip to main content

Setting the bind variable value for the destination View Object in the View Link

I'm sharing an interesting use case that has come up recently from an internal team. The developer was looking for some hooks to set bind variable value used in the destination View Object participating in a View Link. Steve Muench has shared a useful tip to solve this case, let me share the same here for your reference, thanks Steve :)

The use case requirement is to set bind variable value conditionally for the child view object. Interestingly, the bind variable value is supposed to set programmatically only when the child view object is executed as part of master child coordination(through view link), in all other cases it's left to the caller.

The solution is very straight forward. The 11.1.1.5.0 release has new method added ViewObjectImpl::prepareRowSetForQuery(ViewRowSetImpl vrs) to intercept the VO query preparation phase. You may need to override this method, and add check for parent rows(to see whether VO instance is participating in master child coordination) and set the bind variable accordingly.

 @Override  
 public void prepareRowSetForQuery(ViewRowSetImpl vrs) {  
  RowSetIterator[] parentRows = vrs.getMasterRowSetIterators();  
  /**  
  * Check for parent Rows. If its not null, then query execution is   
  * triggered through ViewLink, get the required attributes from   
  * parent row( as per the use case ), and set the bind variable value  
  */  
  if (parentRows != null && parentRows.length > 0 &&  
      parentRows[0].getCurrentRow() != null) {  
      Number locId =  
           (Number)parentRows[0].getCurrentRow().getAttribute("LocationId");  
      //This is just a demo, to hide the employees whose salry is less   
      //than 3000$ from Location = 1500  
      if (locId != null && locId.intValue() == 1500) {  
           vrs.ensureVariableManager().setVariableValue("bindVarSalary",  
                                                                   3000);  
      }  
  }  
  super.prepareRowSetForQuery(vrs);  
 }  

Download

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11.1.1.5.0 (11g R1 PS4) + HR Schema]

A glance at the implementation

This example displays two employee tables, the one which follows master child relation with department table displays only those employee records having salary > 3000. This filtering is done by following the approach detailed above.




Learn More ...

There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html

Comments

Anonymous said…
Thanks for the post.

I have the same requirement. I am using tree table. somehow parent row set is always null. When I tested through the AppModule tester it works fine. Do I need to set something in the UI?
Jobinesh said…
Try overriding VOImpl::createViewLinkAccessorRS(...) on parent VO and hook your code there. More details can be found in this post - http://jobinesh.blogspot.com/2010/01/search-by-child-attributes-on-tree.html
Anonymous said…
Hi,
I know that this question is not related to the above blog.
How can I set a bind variable from the Ui.Eg: Select * from employees where department_id = :dept_id. I tried to use ExecuteWithParams and passed the bind value as 30.But my page is not showing any records in the Ui?

Thanks....
Jobinesh said…
A couple of points to check...
1. If the bind parameter is part of your VO query, it should be marked as Mandatory.
2.If the requirement is to display queried screen when user takes, better drop the ExecuteWithParams as method activity on your adfc-config/taskflow and draw a navigation from method activity to the page such that method activity gets executed whenever user navaigates to page
Anonymous said…
Hi Jobinesh,
I tried the same way what you described in the above post. But when I run the page, the page is not showing any rows.

I gave the value for the bind variable as '30'. In my taskflow, I marked the ExecuteWithParams as the default activity and drawn a navigation to the employee.jsff page. But still am getting the empty table. Any idea..??

Thank you....
Jobinesh said…
I can take a look if you can pass me a sample illustrating the issue
Rolls said…
Hi,

I'm currently using 11.1.1.3 and would like to achieve the same functionality as described in your original blog post. Is there any way to do that?
Anooj said…
I have a query which requires a bind variable. I have created a view link for the same view object.
The bind variable for the Master VO is set but I'm unable to set the bind variable for subsequent detail VOs.
J said…
Hi ,
I am using Version 11.1.1.3 and and want to do the same logic. How do i do it in this version.
Sonal Tuteja said…
hi i want to use the code u gave. but this code is not executing at all. the cotrol is not going there..

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…

Happy New Year 2018 !

We can't go back and change the beginning, but we always can start where we are and change the ending. Believe in yourself and you will be unstoppable!

Wishing you and your family a very happy new year 2018 !!!