| 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 |
Whenever the WHERE clause of your query includes values that might change from execution to execution, you can use named bind variables. These are place holders in the SQL string whose value you can easily change at runtime without altering the text of the SQL string itself. Since the query doesn't change, the database can efficiently reuse the same parsed representation of the query across multiple executions which leads to higher runtime performance of your application.
To add a named bind variable to a view object, use the Bind Variables tab of the Create View Object wizard or the View Object Editor. You can add as many named bind variables as you need. As shown in Figure 5-20, for each bind variable you specify its name, data type, and default value. You can name the variables as you like, but since they share the same namespace as view object attributes you need to choose names that don't conflict with existing view object attribute names. As with view objects attributes, by convention bind variable names are created with an initial capital letter.
On the Control Hints tab, you can also specify UI hints like Label Text, Format Type, Format mask and others, just as you did above with the view object attributes. These bind variable control hints are used automatically by the view layer when you build user interfaces like search pages that allow the user to enter values for the named bind variables. The Updatable checkbox controls whether the end user will be allowed to change the bind variable value through the user interface. If a bind variable is not updatable, then its value can only be changed programmatically by the developer.
After defining the bind variables, the next step is to reference them in the SQL statement. While SQL syntax allows bind variables to appear both in the SELECT list and in the WHERE clause, you'll typically use them in the latter context, as part of your WHERE clause. You could edit the UserList view object created above, and open the SQL Statement page to introduce your named bind variables like this:
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL
Notice that you reference the bind variables in the SQL statement by prefixing their name with a colon like :TheName or :LowUserId. You can reference the bind variables in any order and repeat them as many times as needed within the SQL statement.
Once you've added one or more named bind variables to a view object, you gain the ability to easily see and set the values of these variables at runtime. Information about the name, type, and default value of each bind variable is saved in the view object's XML component definition file. If you have defined UI control hints for the bind variables, this information is saved in the view object's component message bundle file along with other control hints for the view object.
The Business Components Browser allows you to interactively inspect and change the values of the named bind variables for any view object, which can really simplify experimenting with your application module's data model when named bind parameters are involved. The first time you execute a view object in the tester, a Bind Variables dialog will appear, as shown in Figure 5-21. By selecting a particular bind variable in the list, you can see its name as well as both the default and current values. To change the value of any bind variable, just update its corresponding Value field before clicking OK to set the bind variable values and execute the query. Using the Edit Bind Parameters button in the toolbar — whose icon looks like ":id" — you can inspect and set the bind variables for the view object in the current panel.
If you've defined the Label Text, Format Type, or Format control hints, the Bind Variables dialog helps you verify they are correctly setup by showing the label text hint in the Bind Variables list and formatting the Value attribute using the respective format mask. You can see in Figure 5-21 that the label text hints are showing for the three bind variables in the list.
There are several things you may need to know about named bind variables, including the runtime errors that are displayed when bind variables have mismatched names, the default value for bind variables, how to set existing bind variable values at runtime, and how to add a new named bind variable at runtime.
You need to ensure that the list of named bind variables that you reference in your SQL statement matches the list of named bind variables that you've defined on the Bind Variables page of the View Object Editor. Failure to have these two agree correctly can result in one of the following two errors at runtime.
If you use a named bind variable in your SQL statement but have not defined it, you'll receive an error like this:
(oracle.jbo.SQLStmtException) JBO-27122: SQL error during statement preparation. ## Detail 0 ## (java.sql.SQLException) Missing IN or OUT parameter at index:: 1
On the other hand, if you have defined a named bind variable, but then forgotten to reference it or mistyped its name in the SQL, then you will see an error like this:
oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. ## Detail 0 ## java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: LowUserId
The resolution in both cases is to double-check that the list of named bind variables in the SQL matches the list of named bind variables on the Bind Variables page.
If you do not supply a default value for your named bind variable, it defaults to the NULL value at runtime. This means that if you have a WHERE clause like:
USER_ID = :TheUserId
and you do not provide a default value for the TheUserId bind variable, it will default to having a NULL value and cause the query to return no rows. Where it makes sense for your application, you can leverage SQL functions like NVL(), CASE, DECODE(), or others to handle the situation as you require. In fact, the UserList view object uses a WHERE clause fragment like:
upper(FIRST_NAME) like upper(:TheName)||'%'
so that the query will match any name if the value of :TheName is null.
To set named bind variables at runtime, use the setNamedWhereClauseParam() method on the ViewObject interface. You can use JDeveloper's Refactor > Duplicate... feature to create a new TestClientBindVars class based on the existing TestClient.java class from Section 5.7, "How to Create a Command-Line Java Test Client". In this new test client class, you can set the values of the HighUserId and TheName bind variables using the few additional lines of code shown in Example 5-12.
Example 5-12 Setting the Value of Named Bind Variables Programmatically
// changed lines in TestClient class
ViewObject vo = am.findViewObject("UserList");
vo.setNamedWhereClauseParam("TheName","alex%");
vo.setNamedWhereClauseParam("HighUserId", new Number(315));
vo.executeQuery();
// etc.
Running the TestClientBindVars class shows that your bind variables are filtering the data, and the resulting rows are only the two matching ones for Alexander Hunold and Alexander Khoo:
303 ahunold 315 akhoo
Whenever a view object's query is executed, the runtime debug diagnostics show you the actual bind variable values that get used like this:
[256] Bind params for ViewObject: UserList [257] Binding param "LowUserId": 0 [258] Binding param "HighUserId": 315 [259] Binding param "TheName": alex%
This information that can be invaluable in isolating problems in your applications. Notice that since the code did not set the value of the LowUserId bind variable, it took on the design-time specified default value of 0 (zero). Also notice that the use of the UPPER() function in the WHERE clause and around the bind variable ensured that the match using the bind variable value for TheName was performed case-insensitively. The example code set the bind variable value to "alex%" with a lowercase "a", and the results show that it matched Alexander.
Using the view object's setWhereClause() method, you can add an additional filtering clause at runtime. This runtime-added WHERE clause predicate does not replace the design-time one, but rather further narrows the query result by getting applied in addition to any existing design-time WHERE clause. Whenever the dynamically added clause refers to a value that might change during the life of the application, you should use a named bind variable instead of concatenating the literal value into the WHERE clause predicate.
For example, assume you want to further filter the UserList view object at runtime based on the value of the USER_ROLE column in the table. Also assume that you plan to search sometimes for rows where USER_ROLE = 'technician' and other times where USER_ROLE = 'User'. While slightly fewer lines of code, it would be bad practice to do the following because it changes the where clause twice just to query two different values of the same USER_ROLE column:
// Don't use literal strings if you plan to change the value!
vo.setWhereClause("user_role = 'technician'");
// execute the query and process the results, and then later...
vo.setWhereClause("user_role = 'user'");
Instead, add a WHERE clause predicate that references named bind variables that you define at runtime like this:
vo.setWhereClause("user_role = :TheUserRole");
vo.defineNamedWhereClauseParam("TheUserRole", null, null);
vo.setNamedWhereClauseParam("TheUserRole","technician");
// execute the query and process the results, and then later...
vo.setNamedWhereClauseParam("TheUserRole","user");
This allows the text of the SQL statement to stay the same, regardless of the value of USER_ROLE you need to query on. When the query text stays the same across multiple executions, the database give you the results without having to reparse the query.
If you later need to remove the dynamically added WHERE clause and bind variable, you can use code like this:
vo.setWhereClause(null);
vo.removeNamedWhereClauseParam("TheUserRole");
An updated TestClientBindVars class illustrating these techniques would look like what you see in Example 5-13. In this case, the functionality that loops over the results several times has been refactored into a separate executeAndShowResults() method. The program first adds an additional WHERE clause of user_id = :TheUserId and then later replaces it with a second clause of user_role = :TheUserRole.
Example 5-13 TestClient Program Exercising Named Bind Variable Techniques
package devguide.examples.client;
import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
import oracle.jbo.domain.Number;
public class TestClient {
public static void main(String[] args) {
String amDef = "devguide.examples.UserService";
String config = "UserServiceLocal";
ApplicationModule am =
Configuration.createRootApplicationModule(amDef,config);
ViewObject vo = am.findViewObject("UserList");
// Set the two design time named bind variables
vo.setNamedWhereClauseParam("TheName","alex%");
vo.setNamedWhereClauseParam("HighUserId", new Number(315));
executeAndShowResults(vo);
// Add an extra where clause with a new named bind variable
vo.setWhereClause("user_id = :TheUserId");
vo.defineNamedWhereClauseParam("TheUserId", null, null);
vo.setNamedWhereClauseParam("TheUserId",new Number(303));
executeAndShowResults(vo);
vo.removeNamedWhereClauseParam("TheUserId");
// Add an extra where clause with a new named bind variable
vo.setWhereClause("user_role = :TheUserRole");
vo.defineNamedWhereClauseParam("TheUserRole", null, null);
vo.setNamedWhereClauseParam("TheUserRole","user");
// Show results when :TheUserRole = 'user'
executeAndShowResults(vo);
vo.setNamedWhereClauseParam("TheUserRole","technician");
// Show results when :TheUserRole = 'technician'
executeAndShowResults(vo);
Configuration.releaseRootApplicationModule(am,true);
}
private static void executeAndShowResults(ViewObject vo) {
System.out.println("---");
vo.executeQuery();
while (vo.hasNext()) {
Row curUser = vo.next();
System.out.println(curUser.getAttribute("UserId")+" "+
curUser.getAttribute("Email"));
}
}
}
However, if you run this test program, you actually get a runtime error like this:
oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) QRSLT WHERE (user_role = :TheUserRole) ## Detail 0 ## java.sql.SQLException: ORA-00904: "USER_ROLE": invalid identifier
The root cause, which appears after the ## Detail 0 ## in the stack trace, is a SQL parsing error from the database reporting that USER_ROLE column does not exist. That's odd, since the USERS table definitely has a USER_ROLE column. The problem occurs due to the mechanism that ADF view objects use by default to apply additional runtime WHERE clauses on top of read-only queries. Section 5.9.3.5, "Understanding the Default Use of Inline Views for Read-Only Queries", explains a resolution for this issue.
If you dynamically add an additional WHERE clause at runtime to a read-only view object, its query gets nested into an inline view before applying the additional WHERE clause. For example, suppose your query was defined as:
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL
At runtime, when you set an additional WHERE clause like user_role = :TheUserRole as the test program did in Example 5-13, the framework nests the original query into an inline view like this:
SELECT * FROM( select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) 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 where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) QRSLT WHERE user_role = :TheUserRole
This query "wrapping" is necessary in the general case 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 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. The downside that you're seeing here with the ORA-904 error is that the dynamically added WHERE clause can refer only to columns that have been selected in the original query.
Section 27.3.3.7, "Disabling the Use of Inline View Wrapping at Runtime" explains how to disable this query nesting when you don't require it, but for now the simplest solution is to edit the UserList view object and add the USER_ROLE column to the end of its query's SELECT list on the SQL Statement page. Just adding the new column name at the end of the existing SELECT list — of course, preceded by a comma — is enough to do the job: the View Object Editor will automatically keep your view object's attribute list in sync with the query statement.
The modified test program in Example 5-13 now produces the expected results:
--- 303 ahunold 315 akhoo --- 303 ahunold --- 315 akhoo --- 303 ahunold
