FAQ #13 - How to avoid common pitfalls related to expert mode queries in ADF 11g

Introduction

In FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g we have seen one of the pitfalls related to expert mode queries known as query wrapping and the unexpected result it may produce when dynamic criteria are added to the query at runtime. In this FAQ we will see some additional pitfalls related to expert mode queries and how to watch out for them.

Main Theme

Pitfall #1 - Unexpected query results due to query wrapping

Be aware that queries are wrapped when dynamic WHERE and ORDER BY clauses are applied at runtime and of the unexpected results this may have on the execution of your query. We talked extensively about this in FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g.

Pitfall # 2 - Query failure due to dynamic criteria referring to columns not in the original query

Due to query wrapping, dynamic criteria applied at runtime must refer only to columns in the SELECT list of the original query. This can be best demonstrated with the following example.

Let's create a Fusion Web Application (ADF) and a read-only view based on the EMPLOYEES table of the HR schema. We will base the view on the following query.

The view will select the employee id and the employee first and last name. Now let's add an additional attribute called HireDate mapped to the HIRE_DATE column of the EMPLOYEES table, which we will use to further refine the employees for a given hire date range and is not part of the query select list.

We will add dynamic criteria based on bind variables. So first we will create the bind variables inHireDateFrom and inHireDateTo

and then the view criteria based on the bind variables above.

Finally we will create an application module and add the view to its data model. After enabling debug information in the console and testing the application module using the Oracle Business Components Browser, we can observe that a JBO-27122 oracle.jbo.SQLStmtException is thrown when we apply the view criteria at runtime.

The query that was executed is also shown in the Log window:

As you can see, the query is wrapped. The WHERE clause column - the HIRE_DATE - is not referring to any of the SELECT list columns of the original inlined query, which is what actually is causing the exception. To solve this issue, we will need to either disable query wrapping, as explained in FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g, or to ensure that the column used by the dynamically added WHERE clause - the HIRE_DATE -  is added to the SELECT columns of original query.

Pitfall #3 - Row inconsistency errors due to SQL calculations that change entity attributes

This applies when switching an entity-based view object to expert mode and changing an entity-mapped column in the SELECT list expression to include an SQL calculate expression.

This can be demonstrated with the following example. Create an entity object based on the DEPARTMENTS table of the HR schema, a view object based on this entity and finally add the view object to the application module data model. Now, edit the view object and in the Edit Query dialog let's change it to expert mode by selecting Expert from the SQL Mode.

Notice that the query is now editable in the Query Statement edit box. Let's assume now that we have a requirement to display only the first word of the department name and we decide to introduce an SQL-calculate expression for the DEPARTMENT_NAME column as shown below.

When testing the view with the Oracle Business Components Browser at a first glance we will see that the query works fine, returning the first word of the department name just as expected. The problem appears as soon as we attempt to modify a row - a row that the department name has been altered by the SQL-calculate expression we introduced. Try modifying for example department 210 - IT Support. You will get an oracle.jbo.RowInconsistentException: JBO-25014: Another user has changed the row with primary key oracle.jbo.Key[210 ] exception.

This exception is thrown because the department name value in the entity cache - the first word of the department - and the department name value retrieved when locking the row for update - the whole department name - differ. They are indeed inconsistent.

The solution to this pitfall is not to actually alter the entity-mapped column but rather to introduce a new SQL-calculated attribute to fulfill the specific requirement.

Pitfall #4 - Changes to SQL expressions are not reflected in the SQL statement

Basically be aware that the changes you do to the SQL expression in the attribute definition are not carried over to the SQL statement. This is not done by accident - you will need to update the SQL statement yourself to reflect these changes in the attribute.

Pitfall #5 - Limited attribute mapping

Each time you modify the SQL statement SELECT list in expert mode JDeveloper will attempt to remap the view attributes to the query SELECT columns. You will need to ensure that the attribute mappings are correct. To ensure that this is the case, for expert mode queries JDeveloper enables the Attribute Mappings page in the Edit Query dialog.

All of the above and more are very well documented in section 5.9 - Working with View Objects in Expert Mode of the Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) B31974-03 documentation.


Conclusion

Using expert mode queries gives you full control of the SQL query definition in ADF BC. This comes with a cost. So, when using expert query mode, observe and navigate away from the pitfalls associated with it.

Until the next time, keep on JDeveloping!


References

Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) B31974-03 [Section 5.9 Working with View Objects in Expert Mode]


Code

http://jdeveloperfaq.googlecode.com/files/JDeveloperFAQNo13.rar