Declarative SQL Mode Pitfall

As we know a view object definition supports three SQL modes. There are normal, declarative and expert modes. In this post I'm going to focus on the declarative SQL mode. This smart mode allows the framework to automatically build SQL queries at run time according to the attributes asked by the view layer. Furthermore, depending on the attributes being asked, the frameworks builds not only the Select clause but the entire query. So, the From clause contains only entities that are needed to provide asked attributes and as a result of the VO execution only those entities are created and stored in the entity cache. The declarative SQL mode is widely recommended and it became a default mode in JDeveloper 12c. However, we should be careful with this approach since we can run into a small pitfall that I'm going to highlight in this post.
In my example there is a simple VO based on Employees and Departments entities:

The VO is set up to use the declarative SQL mode.
There are a couple of pages in the ViewController project containing a table with employees (Main) and a form with employee's details (Detail):

Both pages share the same VEmployees VO instance, so when we go from the table to the detail form we are going to see the details of the currently selected employee. This is a standard and a very common approach.

So, let's play with this environment a little bit an consider three simple use cases.

Use Case #1. The table contains all the attributes the detail form has:

In this case everything is pretty clear. The VO retrieves all the required attributes with the following query:

SELECT Employees.EMPLOYEE_ID,
                Employees.FIRST_NAME,  
                Employees.LAST_NAME,
                Employees.DEPARTMENT_ID,
                Departments.DEPARTMENT_NAME,
                Departments.DEPARTMENT_ID AS DEPARTMENT_ID1
FROM EMPLOYEES Employees, DEPARTMENTS Departments
WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID

So, when we go from the table to the detail form all attributes of the VO current row are already populated and nothing extra happens.

Use Case #2. The table doesn't contain the DepartmentName but it contains the DepartmentId1 attribute which is the primary key of the Departments entity.

In this case the SQL query looks like this:

SELECT Employees.EMPLOYEE_ID,
                Employees.FIRST_NAME,  
                Employees.LAST_NAME,
                Employees.DEPARTMENT_ID,
                Departments.DEPARTMENT_ID AS DEPARTMENT_ID1
FROM EMPLOYEES Employees, DEPARTMENTS Departments
WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID

So, the DepartmentName has not been selected. However, the department entities have been created and they are stored in the entity cache. But those entities have only one populated attribute DepartmentId. The rest of the attributes in those entities are empty. When we go from the table to the detail form the framework runs into a problem since the DepartmentName attribute of the current VO row is not populated and it forces the entity instance to retrieve itself form the database. And the entity instance fires the following query in order to populate all the attributes it has:

SELECT DEPARTMENT_ID,
               DEPARTMENT_NAME,
               MANAGER_ID,
               LOCATION_ID
FROM DEPARTMENTS Departments WHERE DEPARTMENT_ID=:ID

Use Case #3. The table contains only employees attributes.

In this case the VO executes the following query:

SELECT Employees.EMPLOYEE_ID,
               Employees.FIRST_NAME,  
               Employees.LAST_NAME,
               Employees.DEPARTMENT_ID
 FROM EMPLOYEES Employee

So, there is no Departments in the from clause and as a result of the VO execution there is no any Departments entities created. When we go to the detail form the framework just can't force an entity instance to retrieve the DepartmentName from the database. Because that instance doesn't exist.
Therefore, the detail form contains a blank DepartmentName attribute. Furthermore, in 12c we are going to get an exception in such case:

Actually this is that pitfall that I mentioned at the very beginning of this post. In order to fix the issue in this use case we can add the DepartmentId1 attribute to the tree binding definition of the table:

<tree IterBinding="VEmployeesIterator" id="VEmployees">
  <nodeDefinition
       DefName="com.adfpractice.blog.declarativemodeexample.model.VEmployees"
       Name="VEmployees0">
    <AttrNames>
      <Item Value="EmployeeId"/>
      <Item Value="FirstName"/>
      <Item Value="LastName"/>
      <Item Value="DepartmentId"/>
      <Item Value="DepartmentId1"/>
    </AttrNames>
  </nodeDefinition>
</tree>

This will get the framework to execute the query like in the Use Case #2.
The second option is to set up "Selected in Query" on the DepartmentId1 attribute in the VO definition. This will force the framework to always include the attribute in the Select clause and always include Departments entity in the From clause.

That's it!