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

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of my employer.