ADF how to: errors related to bind variables

I accidentally had found an old manual relating to errors related to "bind variables", so I decided share its fragments:

JBO-27122: SQL error during statement preparation

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

Valid query returns no rows

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.

Error ORA-00904 when adding a Named Bind Variable at Runtime

Assume following situation. Using setNamedWhereClauseParam() method you created code:

ViewObject vo = am.findViewObject("EmployeeList");
vo.setNamedWhereClauseParam("TheName","Jon%");
vo.setNamedWhereClauseParam("HighUserId", new Number(100));
vo.executeQuery();

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 EMPLOYEES 
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 of this error is the mechanism that ADF view objects use by default to apply additional runtime WHERE clauses on top of read-only queries.

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 EMPLOYEES 
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, the framework nests the original query into an inline view like this:

SELECT * FROM(
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME
from EMPLOYEES 
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 EMPLOYEES 
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. In those cases, simply "gluing" the additional runtime onto the end of the query text could produce unexpected results. 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-00904 error is that the dynamically added WHERE clause can refer only to columns that have been selected in the original query.

ADF how to: service methods

All, what do we find regarding "service methods" in ADF BC documentation can be summarized as follows:

"An application module can expose its data model of view objects to clients without requiring any custom Java code. This allows client code to use the ApplicationModule, ViewObject, RowSet, and Row interfaces in the oracle.jbo package to work directly with any view object in the data model. However, just because you can programmatically manipulate view objects any way you want to in client code doesn't mean that doing so is always a best practice.

Whenever the programmatic code that manipulates view objects is a logical aspect of implementing your complete business service functionality, you should encapsulate the details by writing a custom method in your application module's Java class."
 
You can write your own code to configure view object properties to query the correct data to display, you can perform in custom code any kind of multistep procedural logic or finally you can perform aggregate calculations iterating over one or many view objects.

Adding a service method to an application module has many advantages. Some of them are:

• allowing multiple client pages to easily call the same code if needed
• code which can be more clear for the clients (client applications and thier developers)
• option open to improve your implementation without affecting clients
• simplifying of the regression-testing of your complete business service
• enabling declarative invocation of logical business functionality in your pages

To write custom service methods you must enable a custom Java class for an application module:

• In the Application Navigator, double-click the application module.
• In the overview editor, click the Java navigation tab.
  
• On the Java Classes page, click Edit Java Options.
  
• In the Select Java Options dialog, select Generate Application Module Class.
  
• Click OK.

After implementing custom code you need publish its to the client. You can do this by publishing custom methods to the application module's client interface. This can be done manually or by wizard available on the Java Classes page of the overview editor for the application module. To publish methods click the Edit icon in the Client Interface section of the page to display the Edit Client Interface dialog.