Tuesday, January 12, 2010

New Bind Variable and View Criteria bug

In the last patch of jdeveloper ( there are a lot of improvements and bug fixes in view criteria functionality.

1 of the bug fixes is the performance of view criteria that had optional bind variables.

For example if in the Employees View you had optional criterion for departmentId with bind variable ‘inDeptId’ then in the query of the view object with applied view criteria in the where clause it was added ( (Employees.DEPARTMENT_ID = :inDeptId ) or :inDeptId is null )

Yet this adds a large execution time when inDeptId was null since it is forcing a full table scan.

After the last patch when you define optional criterion for departmentId with bind variable ‘inDeptId’ the where added is (Employees.DEPARTMENT_ID = :inDeptId ) and the optional part is implemented by removing the where clause expression.

Yet this dynamic change of query depending on parameters values sometimes throw exception:
(java.sql.SQLException) Missing IN or OUT parameter at index:: 1

I manage to reproduce it in a simple test case in Application Module Browser.

Steps to reproduce:
1. Open EmployeesView1
2. Press Search button (Specify View Criteria)
3. Select EmployeesViewCriteria and press find
4. set departmentId = 10 and press ok
5. Repeat 2-4 but set departmentId = null (empty)

The following exception appears.

If you create a simple search page with query panel and results table then you can’t reproduce this. It is reproduced in our application when we programmatically change view criteria values and execute, but I can’t identify specific conditions

Is this a bug?

Anyone else has run into this?

Test Case

1 comment: