Oracle ADF advanced techniques - Dynamically changing query in view object

Introduction

If you have strange requirements then you may need sometimes to dynamically change SQL query where clause in View Object. I have such requirement when customer said that I can not create additional indexes on table. I overcome that problem by dynamically change SQL query where clause (to use indexes that were available).

To use this functionality, you need to override buildWhereClause(StringBuffer sqlBuffer, int noBindVars) method in view object implementation. You use sqlBuffer parameter in this method (which contains SQL query where in StringBuffer) to change SQL in correlation to you needs. Just replace, remove, add string in this parameter. Just remember to use same bind variables names which will be use in VO SQL call (generic bind variables names are named like :vc_temp_1, :vc_temp_2,...).

Ok, here is the code:

@Override
    protected boolean buildWhereClause(StringBuffer sqlBuffer, int noBindVars) {

        //call super and see if there is where clause.
        boolean hasWhereClause = super.buildWhereClause(sqlBuffer, noBindVars);

        if (hasWhereClause) { 
            //modify existing where query.
        }
        else { 
            //or if you add where clause then return true;
            hasWhereClause = false; 

        }

        return hasWhereClause;
    }