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