Some days ago, in an Oracle MAF technical workshop, I witnessed one of those awkward moments when you are certain that something should work correctly in a presentation but it didn’t. While showing us some basic functionality in Oracle ADF 12c, the instructor implemented a simple query using ViewCriteria. He used an OR conjunction but the query seemed to react like an AND conjunction was selected. This is something I came across a few times while working on Oracle Jdeveloper 12.1.3.0 and I managed to reproduce it exactly.
Let’s say that we are working on our HR schema and we have an instance of our employees with applied criteria for JobId and Salary as in the next screenshot.
The query results in our page is as follows.
Now we’re going to add an OR clause in the second ViewCriteria group to also include the employees of a second department. After doing so, we run our page again and instead of getting the expected results, we get zero rows!
What just happened? Let’s go back again to our ViewCriteria and click edit.
Here we can see something really weird. If you check on the ViewCriteria and the conjunction on the definition of the selected attribute you will see that the OR clause is there. But take a look on the View Object Where Clause. Instead of an OR you see an AND! Our view object seems to be really messed up and we can confirm that by also checking it’s source code:
We can see two things here.
First, the viewCriteriaRow for the second group consists of one ViewCriteria with two ViewCriteriaRows, each one having one ViewCriteriaItem! It’s like a mayhem of ViewCriteria in here! All this code was generated just by adding JobId one more time in the same group.
Second, in the conjunction of the second ViewCriteriaRow (conserning the second JobId operator) we see an AND instead of an OR.
It’s easy to see that this AND is the problem in our query. Try to change this to an OR:
Going back to our Edit View Criteria Wizard, we can check that our Where Clause is also fixed.
Now run our employee page and the expected results are displayed.
Ok, so we found a fix, but what is actually the case? I tried to do the exact same thing but instead of using twice the JobId, I used another attribute. And I didn’t manage to reproduce this. If you add an OR conjunction with a different attribute, the Where Clause and the source code are generated as they should. By adding DepartmentId in our ViewCriteria we see that everything works fine.
And the source code is
Just two ViewCriteriaItems in a ViewCriteriaRow. Plain and simple.
As far as I can tell the problem is the generated source code when adding in the same group the same attribute more than once. Of course in the case where you have an AND conjunction you won’t realize any mistake on runtime. The result of your query will be correct.
Oracle support do provide a patch for this fix (Patch 19608472) and we are expecting this issue to be solved in 12.2.1.0 version. The fix is the same as the one I mentioned, just replacing the AND conjunction with an OR. Though the generated code still remains quite messy as above and if you’d like to work in your ViewCriteria with the same attribute I suggest you keep in mind to check your source code.
In the application development world since 2011. Working with Spring Framework, ADF/SOA/Unified BPM Suite, Oracle Weblogic, Oracle Database.
[…] a year ago I wrote this post about a ViewCriteria issue when using the same attribute twice in Oracle JDeveloper 12.1.3.0. Now […]