Skip to main content

Creating View Criteria having Exists clause at run time

This post is in continuation of one of my old post Creating View Criteria having Bind Variables at run time. In this post I'm sharing sample for creating view criteria with Exists clause at run time. The following code snippet illustrates the API usage for creating View Criteria with Exists clause.

   public void buildVCWithExistsProgrammatically(String departmentName, String email) {  
     //Following code snippet defines  view criteria on DepartmentsView 
     //This view criteria contains 2 ViewCriteriaItem 
     //1.  A normal ViewCriteriaItem for DepartmentName attribute
     //2. The second ViewCriteriaItem  is based on Exists clause
     DepartmentsViewImpl voDept = getDepartmentsView1();  
     VariableValueManager vvm = voDept.ensureVariableManager();  
     ViewCriteria vcDept = voDept.createViewCriteria();  
     ViewCriteriaRow vcrDept = vcDept.createViewCriteriaRow();  
     //This sample uses a normal ViewCriteriaItem DepartmentName
     ViewCriteriaItem vci1 = vcrDept.ensureCriteriaItem("DepartmentName");  
     VariableImpl deptNameVar = (VariableImpl)vvm.addVariable("deptNameBindVarAttribute");  
     deptNameVar.setProperty(AttributeHints.ATTRIBUTE_DISPLAY_HINT, AttributeHints.ATTRIBUTE_DISPLAY_HINT_HIDE);  
     vci1.setValue(0, ":deptNameBindVarAttribute");  
     vci1.setIsBindVarValue(0, true);  
     vvm.setVariableValue(deptNameVar, departmentName + "%");  
     //The following code snippet creates ViewCriteriaItem with
     //Exists clause by accessing   EmployeesView.
     //Note that EmployeesView is an accessor attribute present
     //in DepartmentsView in order to access EmployeesView
     ViewCriteriaItem vci2 = vcrDept.ensureCriteriaItem("EmployeesView");  
     ViewObject accessorVO = vci2.getAccessorVO(vcDept.getViewObject());  
     ViewCriteria nestedVC = accessorVO.createViewCriteria();  
     ViewCriteriaRow vcrEmp = nestedVC.createViewCriteriaRow();  
     vcrEmp.setAttribute("Email", email + "%");  


You can download the sample workspace from here.
Take a look at the buildVCWithExistsProgrammatically() method in the model.AppModuleImpl class. This method generates view criteria with Exists clause for DepartmentsView view object by nesting view criteria from  associated EmployeesView.
[ Runs with Oracle JDeveloper (11g R2PS1) + HR Schema]

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-


Tarun said…
Nice and helpful post..

Though ran in an issue when tried to use multiple view criterion nested inside exists. The requirement is to use "OR" condition inside exits.

Please post an example of that also.

Popular posts from this blog

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 !!!

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…