Skip to main content

Using bind variable for the SQL statements with IN clause

Bind variable improves the performance of query execution by avoiding the repeated parsing of the SQLs (prepare once and execute multiple times). In this post, I'm discussing about the possibility of using bind variable for a ViewObject whose WHERE clause is formed using IN clause.

Obviously, you cannot directly bind a single value to an IN clause and expect it to be treated as many values. A common solution is to have a DB function which takes comma separated String as parameter and let this return a user defined object type. This post is also based on same 'age old' idea. The query generated using the custom db function(in_list_char) to support bind variable for IN clause may look like as shown below,
 SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME  FROM DEPARTMENTS Departments WHERE 
   ( ( Departments.DEPARTMENT_NAME IN (select * from TABLE (cast (in_list_char ( :CommaDelimitedDeptNames ) as ChartableType))A)) )

Generate custom where clause fragment for the ViewCriteriaItem

If you want to enable multi-value search for a specific ViewCriteriaItem in a ViewCriteria, then you can provide the custom interpretation for ViewCriteriaItem by overriding ViewObjectImpl::getCriteriaItemClause(ViewCriteriaItem vci) as shown below.
@Override
public String getCriteriaItemClause(ViewCriteriaItem vci) {
 if (vci.getAttributeDef().getName().equals("DepartmentName") &&
    vci.getViewCriteria().getName().contains("DeptSampleVC")) {
    if (vci.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
    return getINClauseForDatabaseUse(vci);
    } else {
    return getINClauseForCache(vci);
    }
 } else {
    return super.getCriteriaItemClause(vci);
 }

}

protected String getINClauseForDatabaseUse(ViewCriteriaItem vci) {

 String bindVarValue = getCommaDelimitedDeptNames();
 String bindVarName = "CommaDelimitedDeptNames";
 String whereCluase = "1=1";
 if (bindVarValue != null && bindVarValue.trim().length() != 0) {
    whereCluase =
        this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (select /*+ CARDINALITY(A, 50) */ * from TABLE (cast (in_list_char ( :" +
        bindVarName + " ) as ChartableType))A)";
 }
 return whereCluase;
}

protected String getINClauseForCache(ViewCriteriaItem vci) {
 String whereCluase = "1=1"; 
 return whereCluase;
}

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

How to run this sample?

1. Unzip the source to your local drive.
2. Setup the required DB objects in your local schema(HR) by running the <BindParamEnabledINClauseSample>\script\select_in_list.sql
3. Run the test.jspx. This page displays query panel and a result table.
4. This sample has enabled multi-value(comma separated values) search for 'DepartmentName' ViewCriteriaItem.
You can try searching the comma separated values for 'DepartmentName' field in the search panel e.g: Finance,Sales,Executive . Have fun!

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/10/oracle-adf-real-world-developers-guide.html
 

Comments

mukesh said…
Thanks Jobinesh for the info.
May i know what you are trying to do by checking isCriteriaForQuery().. Could you plz eloborate on it?
Jobinesh said…
If you have ViewCriteriaItems based on transient variable, then runtime does the filtering in two passes
1. query against database
criteria.doSetCriteriaMode(ViewCriteria.CRITERIA_MODE_QUERY);
2. second in-memroy
criteria.doSetCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);

The in-memory clause is evaluated after rows are retrieved. Apparently, the overridden getCriteriaItemClause() may need to take care of this scenario as well.
mukesh said…
Thank you for the information.One more doubt that I have is you are returning "1=1" for cache mode. Where does the IN criteria applied for those rows?
ashish said…
=======================
SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME FROM DEPARTMENTS Departments WHERE
( ( Departments.DEPARTMENT_NAME IN (select * from TABLE (cast (in_list_char ( :CommaDelimitedDeptNames ) as ChartableType))A)) )

=========================

Query is NOT working.
Jobinesh said…
Run the sql script included in zip file
I have the same issue, but the work around is not working for me. I am using an IN Clause for a Number.
Jobinesh said…
What is your issue? and what is the work around?
Sagar Mandagiri said…
Hi Sir,

I am New to Adf.
SELECT Products.productid,
Products.productname
FROM dbo.products Products
WHERE Products.productid =? and Products.productname =?
and i bind 2 variables a and b.a is interger and b is string.i did same thing with single parameter ,its working.ie.in the run time itself i can enter the value and got result.but with 2 parameters am not getting result.
its sayng oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT Products.productid, Products.productname FROM dbo.products Products WHERE Products.productid =? and Products.productname =?
Anonymous said…
Hello Sir,
I will be really thankful if you provide me with the from end form design .... my email ID rmanoj.c@gmail.com
Anonymous said…
Hello Sir,
I will be really thankful if you provide me with the front end form design or the GUI page .... my email ID rmanoj.c@gmail.com
Ravindranath said…
Hi Jobinesh,
Nice Post!!
I am trying to apply the IN criteria on the VO - built from a query, I mean this do not have a corresponding EO to it.

this.getEntityDef(0).getAliasName()

Here, how to get the alias name for the VO?

Thanks,
Ravindra

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…