Three SQL Modes in Oracle ADF

There are three SQL modes in Oracle ADF:

1. Declarative SQL mode
2. Normal SQL mode
3. Expert SQL mode

where declarative SQL mode is a new addition in Oracle ADF 11g. The declarative SQL mode for view objects lets you create SQL-Independent view objects . You can define query filter predicates by using view object attribute names without having to type in SQL yourself. At runtime, Oracle ADF translates the logical filter expressions into the appropriate SQL automatically.

Declarative SQL mode selection is supported in JDeveloper as a setting that you can apply either to the entire data model project or to individual view objects that you create. After you enable declarative SQL mode, the basic procedure to create a view object is the same as you would follow to create any entity-based view object.

When you create a new view object, keep the default setting Updatable access through entity objects enabled to indicate that you want this view object to manage data with its base entity object. Any other choice for the data selection will disable declarative SQL mode in the Create View Object wizard.

The alternatives to declarative SQL mode are normal mode and expert mode. When you work in either of those modes, the view object definitions you create at design time always contain the entire SQL statement based on the SQL flavor required by your application module's defined database connection. Thus the capability of SQL independence does not apply to view objects that you create in normal or expert mode. For view objects created in normal or expert mode, you must edit the WHERE clause to filter the data as required.
In declarative SQL mode, the view object's metadata causes the ADF Business Components runtime to generate the SQL query statements as follows:

• Generates SELECT and FROM lists based on the rendered web page's databound UI components' usage of one or more entity objects' attributes
• Optionally, generates a WHERE clause based on a view criteria that you add to the view object definition
• Optionally, generates an ORDERBY clause based on a sort criteria that you add to the view object definition.
• Optionally, augments the WHERE clause to support table joins based on named view criteria that you add to the view object definition
• Optionally, augments the WHERE clause to support master-detail view filtering based on a view criteria that you add to either the source or destination of a view link definition

Enable Declarative SQL Mode Globally

You can enable declarative SQL mode as a global preference so that it is the Create View Object wizard's default mode. If you did not select Enable declarative SQL mode for new objects, in the Preferences dialog, the wizard displays the default query mode, Normal.
Typically, when you define a declarative SQL mode view object, the attributes that get queried at runtime will be determined by the requirements of the databound UI component as it is rendered in the web page. This is the runtime-generation capability that makes view objects independent of the design time database's SQL flavor. However, you may also need to execute the view object programmatically without exposing it to an ADF data binding in the UI. In this case, you can enable the Include all attributes in runtime-generated query option to ensure that a programmatically executed view object has access to all of the entity attributes.

To enable both options (i..e, "Enable declarative SQL mode for new objects" and "Include all attributes in runtime-generated query") for the entire data model project. Go to:

• Tools > Preferences > Business Components > View Objects

Enable Declarative SQL Mode per View Object

To enable Declarative SQL Mode per view object, select the View Object you want to edit. Choose Query tab and click on edit icon (i.e., pencil). On Edit Query dialog, you can choose Declarative SQL Mode (vs. Normal or Expert Mode).

Comparisons of Three SQL Modes

• Normal Mode
• You can manually enter custom WHERE and ORDER BY clauses in a view object query. However, by default, the FROM clause and SELECT list are automatically derived. When you require full control over the SELECT or FROM clause in a query, you can enable expert mode.
• The automatic cooperation of a view object with its underlying entity objects depends on correct attribute-mapping metadata saved in the XML component definition. This information relates the view object attributes to corresponding attributes from participating entity usages. JDeveloper maintains this attribute mapping information in a fully automatic way for normal entity-based view objects.
• Expert Mode
• In Expert Mode, you are given full control over the entire SQL statement. However, it provides limited Attribute Mapping Assistance.
• When you enable expert mode, the read-only Generated Statement section of the Query page becomes a fully editable Query Statement text box, displaying the full SQL statement. Using this text box, you can change every aspect of the SQL query.
• Declarative SQL Mode
• The view object in declarative SQL mode generates all SQL clauses entirely at runtime. The runtime-generated SQL statements will be determined by the SQL flavor specified in the Business Components page of the Project Properties dialog. Currently, the runtime supports SQL92 (ANSI) style and Oracle style flavors.
• In declarative SQL mode, since the wizard and editor do not allow you to enter WHERE and ORDERBY clauses, you provide equivalent functionality by defining a view criteria and sort criteria respectively.
• In declarative SQL mode, view criteria and sort criteria appear in the view object metadata definition and will be converted at runtime to their corresponding SQL clause.
• In this mode, Query Clauses section changes to show declarative controls for the Where filter expression and the Order By attribute list.
• Just as with normal mode view objects, you can link view objects that you create in declarative SQL mode to other view objects to form master-detail hierarchies of any complexity. However, in the case of view objects that you create in declarative SQL mode, you can further refine the view object results in the Source SQL or Destination SQL dialog for the view link by selecting a previously defined view criteria in the Create View Link wizard or the overview editor for the view link.

More Readings

• Declarative Data Filtering
• Working with View Objects in Declarative SQL Mode