OAF LOV Overriding Default Where Clause

Oracle SQL never seems to like function in the WHERE clause and if the function got an indexed column as parameter then most likely the query will not perform good.

Say for example,

SELECT emp_no
FROM emp_table
WHERE UPPER(emp_name) like 'JOHN%'

If emp_table have an index on emp_name, the above query will not pick that index.

The default behavior of Oracle Framework (OAF) LOV is to add where clause with UPPER function.

SELECT * FROM (
SELECT emp_no
FROM emp_table) QRSLT
WHERE (( UPPER(emp_name) like :1 AND (emp_name like :2 OR emp_name like :3 OR emp_name like :4 OR emp_name like :5)))

And it binds values like 'JOHN%', 'John%','john%' and 'jOHN%'.



You might end up with performance issue due to this and if OAF suggested way of making the criteria passive is to0 painful then simply add the following code in the VOImpl


public void initQuery(Dictionary[] x)
{
if( x!= null)
{
for(int i = 0 ; i < x.length ; i++)
{
Dictionary z = x[i];
z.remove(OAViewObjectImpl.CRITERIA_LOOKUP_NAME);
}
}
super.initQuery(x);
}