Monday, January 18, 2010

Selectively required criteria in LOV (Fixed in 11.1.1.3)

In order to improve performance in LOVs I tried to set selectively required criteria, so that the query of LOV should always run with some parameters.
So in simple department LOV in employees.departmentId attribute I put view criteria with selectively required option.



Also as in test case :
http://adfbugs.blogspot.com/2009/08/lov-execute-query-many-times.html
I override the method executeQuery for collection and print the query:

@Override
protected void executeQueryForCollection(Object object, Object[] object2, int i) {
System.out.println("EmployeesLOV executes Query :" + this.getQuery());
super.executeQueryForCollection(object, object2, i);
}

When I run application and open LOV and select a value, in log I see that 3!? Queries are executed:

Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments WHERE ( ( (UPPER(Departments.DEPARTMENT_NAME) LIKE UPPER( :vc_temp_1 '%') ) ) )
Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments WHERE ( ( ( (UPPER(Departments.DEPARTMENT_NAME) LIKE UPPER( :vc_temp_1 '%') ) ) ) AND ( ( (Departments.DEPARTMENT_ID LIKE :vc_temp_2 ) ) AND ( ( (UPPER(Departments.DEPARTMENT_NAME) LIKE UPPER( :vc_temp_3 '%') ) ) ) ) )
Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments

The Last query without any criteria !?

If I just type a valid value to the field and press tab then 4!? Queries are executed:

Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments WHERE ( ( (Departments.DEPARTMENT_ID LIKE ( :vc_temp_1 '%') ) ) )
Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments WHERE (Departments.DEPARTMENT_ID = :fbkKy__0)
Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments WHERE ( ( (Departments.DEPARTMENT_ID LIKE :vc_temp_1 ) ) AND ( ( (UPPER(Departments.DEPARTMENT_NAME) LIKE UPPER( :vc_temp_2 '%') ) ) ) )
Query : SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Departments.MANAGER_ID, Departments.LOCATION_ID FROM DEPARTMENTS Departments

The Last query again without any criteria !?

So you don’t gain much in performance of LOV since the query is executed many times again and without any criteria
.

Also sometimes when you just type a value, you get the PPR exception, or an invalid value exception even if the value is valid.

Steps to reproduce:
1. Run TestLOV page
2. open department LOV, type A in DepartmentName criterion and press search
3. Without selecting a row press OK or cancel.
4. Type a valid value to LOV (i.e. 50) invalid value message appear.


5. Open LOV again, press OK or cancel then press submit button



oracle.jbo.AttrValException: JBO-27036: At least one of the following attributes DepartmentName, ManagerId is required. at oracle.jbo.rules.JboVCItemRequiredValidator.validate(JboVCItemRequiredValidator.java:172) at oracle.jbo.common.ViewCriteriaImpl.validateRow(ViewCriteriaImpl.java:606)

It seems that Selectively required criteria in LOV has some issues.

Test Case :
http://adfbugs.googlecode.com/files/TestLovSelectivelyRequired.zip

2 comments: