ViewCriteria issue when using same attribute twice (Oracle JDeveloper 12.1.3.0)

Rigas Papazisis Fusion Middleware, Oracle ADF 1 Comment

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.

 

viewCriteria1

The query results in our page is as follows.

employees1

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!

employees2

What just happened? Let’s go back again to our ViewCriteria and click edit.

viewCriteria2

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:

sourceCode1

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:

sourceCode2

Going back to our Edit View Criteria Wizard, we can check that our Where Clause is also fixed.

viewCriteria3

Now run our employee page and the expected results are displayed.

employees3

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.

viewCriteria4

And the source code is

sourceCode3

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.

Rigas Papazisis

Rigas Papazisis

Senior Software Engineer at Nodalpoint Systems
Oracle ADF Certified Implementation Specialist.
In the application development world since 2011. Working with ADF/SOA/Unified BPM Suite, Oracle Weblogic, Oracle Database.
Rigas Papazisis

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
0 Comment authors
ViewCriteria issue when using more than once attribute with LOV based on EL expression (Oracle JDeveloper 12.2.1.0) - Nodalpoint Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
trackback

[…] 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 […]