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

FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g

Introduction

In ADF, when using read-only views, query "wrapping" refers to nesting of the original query into an inline view. In some cases, query "wrapping" could produce unexpected results and should be disabled. This FAQ shows you how.

Main Theme

When creating read-only view objects where the query is entered manually by the developer, ADF wraps the original query during its execution at runtime and then applies additional view criteria if needed. This is necessary because the original query could be complex enough - for example combining multiple queries using a UNION operator - and simply applying the view criteria directly on the query it will apply it only on the last UNIONed statement. Let's take a closer look and illustrate with an example.

Start JDeveloper and create a sample Fusion Web Application (ADF). Right-click on the Model project and select New.... From the New Gallery dialog select View Object from the ADF Business Components category under Business Tier.

In the Initialize Business Components Project dialog create a new connection to the HR schema, select it and click OK.

In the Create View Object - Step 1 of 9 page, enter the name of the View Object, select Read-only access through SQL for the data source and click Next.

In the Create View Object - Step 2 of 9 page, enter the query directly as shown below.

Click Next for all other pages and in the last page of the Create View Object wizard click Finish to create the View Object.

Now create an Application Module and add the View Object created above into the data model.

Finally, add debug logging to the console - refer to FAQ #2 – How to enable debug information generation for an ADF BC project, right-click the application module and run it. In the Oracle Business Component Browser, double-click the Employees view object in the tree to execute its query. In the Log window observe that the query that was executed is identical to the one we entered when we created the view object. No query wrapping.

The framework will introduce query wrapping once a dynamic WHERE clause is applied to the query at runtime. To simulate this, we will refine the query results by creating view criteria based on a bind variable. In the Query page of the view object add a bind variable as shown below.

Then add the View Criteria based on the bind variable we just created as shown below.

Now run again the Oracle Business Component Browser by right-clicking on the application module and selecting Run. Double-click on the Employees view to execute it and then apply the view criteria by clicking on the Specify View Criteria icon.

Shuttle the view criteria to the Selected list and click Find in the Business Component View Criteria dialog.

In the Bind Variables dialog specify a department identifier and click OK to apply the view criteria.

Observe the Log window as the view executes the query with the view criteria applied. The original query is wrapped.

This is OK until a new requirement is introduced for the Employees view to produce the total salary amount for the department. Piece of cake you say. So we edit the query and we add a sum on the salary.

We save the query and we run the Oracle Business Components Browser once more. We apply the view criteria for the specific department as before only to find out that the department salary produced is not specific to the department but the grand total of all salaries. This becomes obvious once you take a look at the query in the Log window. The original query is wrapped and the view criteria applied on the inlined view.

Resolving this issue will require us to disable query wrapping. This is pretty straightforward. The framework supplies a function called setNestedSelectForFullSql() which accepts a true/false boolean to enable/disable query wrapping respectively. So, we generate a java implementation class for the view object and call setNestedSelectForFullSql() with false in the constructor.

Now we can re-run the Oracle Business Components Browser and verify the expected results. Re-applying the same view criteria, this time the result is as expected.

Observe that no query wrapping occurs this time around.

Conclusion

Query wrapping is introduced by the ADF framework for expert mode queries in read-only views when a dynamic WHERE clause is applied at runtime. In some cases, when this produces unexpected results, we can call setNestedSelectForFullSql() with false as an argument to disable it.

Until the next time, keep on JDeveloping!

Code
http://jdeveloperfaq.googlecode.com/files/JDeveloperFAQNo12.rar