| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
When defining entity-based view objects, you can fully-specify the WHERE and ORDER BY clauses, whereas, by default, the FROM clause and SELECT list are automatically derived. The names of the tables related to the participating entity usages determine the FROM clause, while the SELECT list is based on the:
Underlying column names of participating entity-mapped attributes
SQL expressions of SQL-calculated attributes
When you require full control over the SELECT or FROM clause in a query, you can enable "Expert Mode".
To enable expert mode, select Expert Mode on the SQL Statement panel of the Create View Object wizard or View Object Editor.
When you enable expert mode, the read-only Generated Statement section of the SQL Statement panel 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.
For example, Example 27-4 shows the SQL Statement page of the View Object editor for the SRDemo application's ServiceHistories view object. It's an expert mode, entity-based view object that references a PL/SQL function context_pkg.app_user_name and joins the USERS table an additional time in the FROM clause to filter hidden service history notes from end-users who are not in the technician or manager roles.
{para}?>
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. However, when you decide to use expert mode with a view object, you need to pay attention to the changes you make to the SELECT list. That is the part of the SQL query that directly relates to the attribute mapping. Even in expert mode, JDeveloper continues to offer some assistance in maintaining the attribute mapping metadata when you do the following to the SELECT list:
Reorder an expression without changing its column alias
JDeveloper reorders the corresponding view object attribute and maintains the attribute mapping.
Add a new expression
JDeveloper adds a new SQL-calculated view object attribute with a corresponding Camel-Capped name based on the column alias of the new expression.
Remove an expression
JDeveloper converts the corresponding SQL-calculated or entity-mapped attribute related to that expression to a transient attribute.
However, if you rename a column alias in the SELECT list, JDeveloper has no way to detect this, so it is treated as if you removed the old column expression and added a new one of a different name.
After making any changes to the SELECT list of the query, visit the Attribute Mappings panel to ensure that the attribute mapping metadata is correct. The table on this panel, which is disabled for view objects in normal mode, becomes enabled for expert mode view objects. For each view object attribute, you will see its corresponding SQL column alias in the table. By clicking into a cell in the View Attributes column, you can use the dropdown list that appears to select the appropriate entity object attribute to which any entity-mapped view attributes should correspond.
|
Note: If the view attribute is SQL-calculated or transient, a corresponding attribute with a ÒSQLÓ icon appears in the View Attributes column to represent it. Since these attributes are not related to underlying entity objects, there is no entity attribute related information required for them. |
When you disable expert mode for a view object it will return to having its SELECT and FROM clause be derived again. JDeveloper warns you that doing this might lose any of your custom edits to the SQL statement. If this is what you want, after acknowledging the alert, your view object's SQL query reverts back to the default.
Consider a Products view object with a SQL-calculated attribute named Shortens whose SQL expression you defined as SUBSTR(NAME,1,10). If you switch this view object to expert mode, the Query Statement box will show a SQL query like this:
SELECT Products.PROD_ID,
Products.NAME,
Products.IMAGE,
Products.DESCRIPTION,
SUBSTR(NAME,1,10) AS SHORT_NAME
FROM PRODUCTS Products
If you go back to the attribute definition for the Shortens attribute and change the SQL Expression field from SUBSTR(NAME,1,10) to SUBSTR(NAME,1,15), then the change will be saved in the view object's XML component definition. Note, however, that the SQL query will remain as above. This occurs because JDeveloper never tries to modify the text of an expert mode query. In expert mode, the developer is in full control. JDeveloper attempts to adjust metadata as described above in function of some kinds of changes you make yourself to the expert mode SQL statement, but it does not perform the reverse. Therefore, if you change view object metadata, the expert mode SQL statement is not updated to reflect it.
To make the above change to the SQL calculated Shortens attribute, you need to update the expression in the expert mode SQL statement itself. To be 100% thorough, you should make the change both in the attribute metadata and in the expert mode SQL statement. This would ensure — if you (or another developer on your team) ever decides to toggle expert mode off at a later point in time — that the automatically derived SELECT list would contain the correct SQL-derived expression.
|
Note: If you find you had to make numerous changes to the view object metadata of an expert mode view object, you can consider the following technique to avoid having to manually translate any effects those changes might have implied to the SQL statement yourself. First, copy the text of your customized query to a temporary file. Then, disable expert mode for the view object and acknowledge the warning that you will lose your changes. At this point JDeveloper will re-derive the correct generated SQL statement based on all the new metadata changes you've made. Finally, you can enable expert mode once again and re-apply your SQL customizations. |
When changing the SELECT list expression that corresponds to entity-mapped attributes, don't introduce SQL calculations that change the value of the attribute when retrieving the data. To illustrate the problem that will occur if you do this, consider the following query for a simple entity-based view object named Products:
SELECT Products.PROD_ID,
Products.NAME,
Products.IMAGE,
Products.DESCRIPTION
FROM PRODUCTS Products
Imagine that you wanted to limit the name column to showing only the first ten characters of the name for some use case. The correct way to do that would be to introduce a new SQL-calculated field called ShortName with an expression like SUBSTR(Products.NAME,1,10). However, one way you might have thought to accomplish this was to switch the view object to expert mode and change the SELECT list expression for the entity-mapped NAME column to the following:
SELECT Products.PROD_ID,
SUBSTR(Products.NAME,1,10) AS NAME,
Products.IMAGE,
Products.DESCRIPTION
FROM PRODUCTS Products
This alternative strategy would initially appear to work. At runtime, you see the truncated value of the name as you are expecting. However, if you modify the row, when the underlying entity object attempts to lock the row it does the following:
Issues a SELECT FOR UPDATE statement, retrieving all columns as it tries to lock the row.
If the entity object successfully locks the row, it compares the original values of all the persistent attributes in the entity cache as they were last retrieved from the database with the values of those attributes just retrieved from the database during the lock operation.
If any of the values differs, then the following error is thrown:
(oracle.jbo.RowInconsistentException) JBO-25014: Another user has changed the row with primary key [...]
If you see an error like this at runtime even though you are the only user testing the system, it is most likely due to your inadvertently introducing a SQL function in your expert mode view object that changed the selected value of an entity-mapped attribute. In the example above, the SUBSTR(Products.NAME,1,10) function introduced causes the original selected value of the Name attribute to be truncated. When the row-lock SQL statement selects the value of the NAME column, it will select the entire value. This will cause the comparison described above to fail, producing the "phantom" error that another user has changed the row.
The same thing would happen with NUMBER, or DATE valued attributes if you inadvertently apply SQL functions in expert mode to truncate or alter their retrieved values for entity-mapped attributes. If you need to present altered versions of entity-mapped attribute data, introduce a new SQL-calculated attribute with the appropriate expression to handle the job.
When you switch a view object to expert mode, its XML component definition switches from storing parts of the query in separate XML attributes, to saving the entire query in a single SQLQuery element. The query is wrapped in a XML CDATA section to preserve the line formatting you may have done to make a complex query be easier to understand.
If your expert-mode view object:
Contains a design-time ORDER BY clause specified in the Order By field of the Query Clauses panel, or
Has a dynamic where clause or order by clause applied at runtime using setWhereClause() or setOrderByClause()
then its query gets nested into an inline view before applying these clauses. For example, suppose your expert-mode query was defined as:
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS union all select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from INACTIVE_USERS
At runtime, when you set an additional WHERE clause like email = :TheUserEmail, the view object nests its original query into an inline view like this:
SELECT * FROM( select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS union all select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from INACTIVE_USERS) QRSLT
and then adds the dynamic where clause predicate at the end, so that the final query the database sees is:
SELECT * FROM( select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS union all select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from INACTIVE_USERS) QRSLT WHERE email = :TheUserEmail
This query "wrapping" is necessary in general for expert mode queries since the original query could be arbitrarily complex, including SQL UNION, INTERSECT, MINUS, or other operators that combine multiple queries into a single result. In those cases, simply "gluing" the additional runtime WHERE clause onto the end of the query text could produce unexpected results since. For example, it might only apply to the last of several UNION'ed statements. By nesting the original query verbatim into an inline view, the view object guarantees that your additional WHERE clause is correctly used to filter the results of the original query, regardless of how complex it is.
Due to the inline view wrapping of expert mode view objects, the dynamically-added WHERE clause can only refer to columns in the SELECT list of the original query. To avoid this limitation, when necessary you can disable the use of the inline view wrapping by calling setNestedSelectForFullSql(false).
When you modify a query to be in expert mode after you have already created:
View links involving it, or
Other view objects that extend it
JDeveloper will warn you with the alert shown in Figure 27-3 to remind you that you should revisit these dependent components to ensure their SQL statements still reflect the correct query.
For example, if you were to modify the ServiceRequests view object in the SRDemo application to use expert mode, since the ServiceRequestsByStatus view object extends it, you need to revisit the extended component to ensure its query still logically reflects an extension of the modified parent component.
